date function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hervinder - 16 May 2006 14:59 GMT

I need help with data that i have exported from a database.

The date column is in a format i havent seen before

6093 Basically the "6" is for 2006, "093" is 93 days since the first of Jan.

Can someone please help me use the date function to get this into dd/mm/yy
format.

Thanks in advance
Hervinder

Reply to this Message

Niek Otten - 16 May 2006 15:09 GMT

=DATE(LEFT(A1,1)+2000,1,1)+MOD(A1,1000)

Format as dd/mm/yy

Signature

Kind regards,

Niek Otten

|I need help with data that i have exported from a database.
|
[quoted text clipped - 7 lines]
| Thanks in advance
| Hervinder

Reply to this Message

Toppers - 16 May 2006 15:12 GMT

A search on Google found the following for converting Julian Dates to a
Standard Date (2000 is the century):

A1 contains the Julian Date i.e 6093 or 06093

=DATE(2000+INT(A1/1000),1,MOD(A1,1000))

HTH

> I need help with data that i have exported from a database.
>
[quoted text clipped - 7 lines]
> Thanks in advance
> Hervinder

Reply to this Message

Gary''s Student - 16 May 2006 15:20 GMT

If 6000 corresponds to Sunday, January 01, 2006
then:
=DATE(2000+LEFT(A1,1),1,RIGHT(A1,3)+1) would display:
Tuesday, April 04, 2006
for 6093

Signature

Gary's Student

> I need help with data that i have exported from a database.
>
[quoted text clipped - 7 lines]
> Thanks in advance
> Hervinder

Reply to this Message

Toppers - 16 May 2006 15:29 GMT

Question: Is it 93 days since the 1st Jan i.e 94th day or the 93rd day of
2006 i.e a Julian Date?

My earlier response assumed the latter but the other replies assumed the
former i.e 6000 is Jan 1st which seems slightly unusual (to me!).

> I need help with data that i have exported from a database.
>
[quoted text clipped - 7 lines]
> Thanks in advance
> Hervinder

Reply to this Message

David Biddulph - 16 May 2006 16:40 GMT

>I need help with data that i have exported from a database.
>
[quoted text clipped - 5 lines]
> Can someone please help me use the date function to get this into dd/mm/yy
> format.

See the other group where you asked the same question. Please don't post
separately to different groups; if you must use different groups, then
crosspost. People will get fed up reading the same question on different
groups, and they will be annoyed if they waste time answering a question
which has already been answered elsewhere.

Signature

David Biddulph

Reply to this Message