how to convert from julian date to mm/dd/year

[Guest]

My data was originally in accounting system as standard date but when I have
to query database (using MicroSoft Access), the data results for dates have
been converted to julian. I cannot find my old formula to allow me to
convert julian dates back into standard date format such as mm/dd/yr. Can
anyone please help?

Thank you,
Robin

 

[Earl Kiosterud]

Robin,

First of all you need to be sure they're really julian, not just unformatted date-serial
numbers used by Excel and Access. The Excel date-serial number for today's date (Sep 8) is
39333. The julian is 07251. If they're Excel date-serials, try Format - Cells - Number
tab - Date, and apply any desired date format.

See Chip Pearson's page on Julian dates for more info, and formulas to do conversions.
http://www.cpearson.com/excel/jdates.htm
-
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[MartinW]

Hi Robin,

Chip Pearson explains it here.
http://www.cpearson.com/excel/jdates.htm

HTH
Martin

 

[Guest]

Hi Robin,

Assuming that the date is in cell A1 then the following should do the trick.
With this formula it does not matter if Julian date has 2 or 4 digits. eg for
Sep 8 2007 the Julian date can be 07251 or 2007251. The nested formula
LEFT(A1,LEN(A1)-3) takes care of it by subtracting the 3 characters
representing the number of days from beginning of year from the total number
of characters.

=VALUE(TEXT("1/1/"&LEFT(A1,LEN(A1)-3),"mm/dd/yyyy"))+VALUE(RIGHT(A1,3))-1

You will need to format the cells with the formula to mm/dd/yy. (Or any
other valid date format that you prefer)

Regards,

OPssieMac

 

[Guest]

Thank you Martin...saved me much dusting of the brain cells where the Excel
tricks are stored...especially when the project is due Monday AM!

 

[Guest]

Thank you OssieMac....I love the cell play-by-play....works beautifully!

 

[Guest]

Hey Earl...thanks for checking on the posibities of being a seriel number but
it was a jewel of a julian. Thanks for the answers!
Regards from
Houston,

Robin