That's what I'm looking for - the 1 in front. Thanks

"Bernie Deitrick" wrote:

Robin,

Julian dates are determinate: there is only one day that can have a Julian
date of a certain value. The modified Julian style date that you are using
doesn't allow you to automatically determine whether 50123 is the 123rd day
of 1950 or of 2050 (or 1850, 1750, 2150, for that matter). So you would
need to modify the formula from that MS page to

=IF(LEFT(TEXT(Standard_Date,"yyyy"),2)="20","1","0 ")
&TEXT(Standard_Date,"yy")&TEXT((Standard_Date-
DATEVALUE("1/1/"&TEXT(Standard_Date,"yy"))+1),"000")

This will put a 1 in front of years starting with 20 and a 0 for those
starting with 19.

If you are one of those purists who insist that 2000 is part of the 20
century, and not the 21st century, then you could use:

=IF(VALUE(TEXT(Standard_Date,"yyyy"))2000,"1","0" )
&TEXT(Standard_Date,"yy")&TEXT((Standard_Date-
DATEVALUE("1/1/"&TEXT(Standard_Date,"yy"))+1),"000")

HTH,
Bernie
MS Excel MVP


"Robin" wrote in message
...
That's how Microsoft converts 5/3/05 to Julian (see Barb R.'s post) and
it's
also how JDE OneWorld sees that date as Julian. So if it's not Julian,
whatever it is, at least they match. That's what counts for what I'm
doing.

"Bernie Deitrick" wrote:

Robin,

05123 is not a Julian date. Does is mean the 123rd day of the 5 year,
perhaps?

HTH,
Bernie
MS Excel MVP


"Robin" wrote in message
...
I have to convert a date to a Julian date to import it into JDE
OneWorld.
The problem is the OneWorld also needs the century at the beginning of
the
Julian number (i.e., Julian date of 05123 needs to be 105123). Anyone
have a
way of determining the century?