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 (E-Mail Removed)> wrote in message
news:F2189DBE-2F60-4A24-947F-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in
message news:F2189DBE-2F60-4A24-947F-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in
> message news:F2189DBE-2F60-4A24-947F-(E-Mail Removed)...
> > 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 (E-Mail Removed)> wrote in message
> news:F2189DBE-2F60-4A24-947F-(E-Mail Removed)...
> > 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
>
>
>