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

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?

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?

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

"Robin" wrote:

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?

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

"Robin" wrote:

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?

I got that formula to get the Julian date. It's just the tacking the century
on the front that I'm stuck on. Right now, I just hard-coded a "1" in front.

"Barb R." wrote:

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

"Robin" wrote:

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?

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?

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?

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?

On Fri, 27 May 2005 08:32:11 -0700, "Robin"
wrote:

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