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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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...
 
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
 
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!
 
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
 
Back
Top