Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions | |||
Using a col of Dates by day I want to determine a wk ending date. | Excel Worksheet Functions | |||
Excel formula with date constraints | Excel Discussion (Misc queries) | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions |