Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
=DATE(LEFT(A1,1)+2000,1,1)+MOD(A1,1000)
Format as dd/mm/yy -- Kind regards, Niek Otten "Hervinder" wrote in message ... |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 | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
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 "Hervinder" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
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 -- Gary's Student "Hervinder" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
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!). "Hervinder" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
"Hervinder" wrote in message
... 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. -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date function in Excel that updates only when a doc is changed? | Excel Worksheet Functions | |||
Date Function | Excel Discussion (Misc queries) | |||
date format and the RIGHT function | Excel Worksheet Functions | |||
Date function | Excel Worksheet Functions | |||
Is there a function to show future date | Excel Worksheet Functions |