How can I determine the century of a date in Excel?

[Guest]

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?

 

[Bernie Deitrick]

Robin,

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

Does this help:
http://support.microsoft.com/?kbid=214099

 

[Guest]

You could get the year using the formula YEAR( ) and parse out what you need,
I suppose.

 

[Guest]

I got that formula to get the Julian date. It's just the tacking the century

 

[Guest]

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]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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?

=INT(TEXT(Standard_Date,"yyyy")/100)-19

will return 1 for 2000+ years and 0 for 1900-2000 years.

Is that what you want?


--ron