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...
-----------------------------------------------------------------------
"robin watersong" <robin wrote in message
...
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

Hi Robin,

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

HTH
Martin



"robin watersong" <robin wrote in
message ...
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

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



"robin watersong" wrote:

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

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!

"MartinW" wrote:

Hi Robin,

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

HTH
Martin



"robin watersong" <robin wrote in
message ...
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

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

"OssieMac" wrote:

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



"robin watersong" wrote:

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

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

"Earl Kiosterud" wrote:

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...
-----------------------------------------------------------------------
"robin watersong" <robin wrote in message
...
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