date function

[Guest]

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

 

[Niek Otten]

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

Format as dd/mm/yy

--
Kind regards,

Niek Otten

|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
|

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!).

 

[David Biddulph]

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.

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.