| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
test to Julian date
Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be
converted to Julian date. Can this be done without first converting to date format? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
Hi
Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
That almost did the trick. I need the two digit year in front as in: 3/20/1996
to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
Maybe:
=MID(A1,3,2) &TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1),"000") curtev wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
With
A1: (a number representing YYYYMMDD) Try this: =MID(A1,3,2)&MID(1000+TEXT(A1,"0000\/00\/00")-("12/31/"&(LEFT(A1,4)-1)),2,3) Does that help? *********** Regards, Ron XL2002, WinXP "curtev" wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
Thank you Dave and Roger for your help.
For 19960320, using your formula I am getting 96079, but it should be 96080. "Dave Peterson" wrote: Maybe: =MID(A1,3,2) &TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1),"000") curtev wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
Ron, that works great. Thank you all for your help and patience.
I am confused, however. Using Excel's formula of =RIGHT(YEAR(G1),2)&TEXT(G1-DATE(YEAR(G1),1,0),"000") on date format of 3/20/1996, it returns 96080 But using a converter on the Internet, and the first two formulas above, I get 96079. Why is that? Which is correct? Thank you very much for your help. "Ron Coderre" wrote: With A1: (a number representing YYYYMMDD) Try this: =MID(A1,3,2)&MID(1000+TEXT(A1,"0000\/00\/00")-("12/31/"&(LEFT(A1,4)-1)),2,3) Does that help? *********** Regards, Ron XL2002, WinXP "curtev" wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
It would depend on whether you wanted to include that end day:
=MID(A1,3,2) &TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,0),"000") notice that this portion: DATE(LEFT(A1,4),1,0) pretty much matches DATE(YEAR(G1),1,0) that you posted in the other branch. curtev wrote: Thank you Dave and Roger for your help. For 19960320, using your formula I am getting 96079, but it should be 96080. "Dave Peterson" wrote: Maybe: =MID(A1,3,2) &TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1),"000") curtev wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
Aha. I have just realized that this is a leap year, therefore the different
answers. Two formulas are needed. One for a regular year and another for a leap year. So I guess I need an IF statement to determine which formula to use. Can you help me with that? Again thank you for your help. "curtev" wrote: Ron, that works great. Thank you all for your help and patience. I am confused, however. Using Excel's formula of =RIGHT(YEAR(G1),2)&TEXT(G1-DATE(YEAR(G1),1,0),"000") on date format of 3/20/1996, it returns 96080 But using a converter on the Internet, and the first two formulas above, I get 96079. Why is that? Which is correct? Thank you very much for your help. "Ron Coderre" wrote: With A1: (a number representing YYYYMMDD) Try this: =MID(A1,3,2)&MID(1000+TEXT(A1,"0000\/00\/00")-("12/31/"&(LEFT(A1,4)-1)),2,3) Does that help? *********** Regards, Ron XL2002, WinXP "curtev" wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
I'd do some more testing with that theory.
curtev wrote: Aha. I have just realized that this is a leap year, therefore the different answers. Two formulas are needed. One for a regular year and another for a leap year. So I guess I need an IF statement to determine which formula to use. Can you help me with that? Again thank you for your help. "curtev" wrote: Ron, that works great. Thank you all for your help and patience. I am confused, however. Using Excel's formula of =RIGHT(YEAR(G1),2)&TEXT(G1-DATE(YEAR(G1),1,0),"000") on date format of 3/20/1996, it returns 96080 But using a converter on the Internet, and the first two formulas above, I get 96079. Why is that? Which is correct? Thank you very much for your help. "Ron Coderre" wrote: With A1: (a number representing YYYYMMDD) Try this: =MID(A1,3,2)&MID(1000+TEXT(A1,"0000\/00\/00")-("12/31/"&(LEFT(A1,4)-1)),2,3) Does that help? *********** Regards, Ron XL2002, WinXP "curtev" wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
test to Julian date
I've seen a fair amount of confusion about what a julian date is. I followed
Chip Pearson's coverage them at his website: http://www.cpearson.com/excel/jdates.htm Per his first formula, the julian date for 20-MAR-1996 is 96080. In a leap year, 20-MAR-1996 is the 80th day, otherwise it's the 79th. The formula I posted accommodates both. Does that help? *********** Regards, Ron XL2002, WinXP "curtev" wrote: Aha. I have just realized that this is a leap year, therefore the different answers. Two formulas are needed. One for a regular year and another for a leap year. So I guess I need an IF statement to determine which formula to use. Can you help me with that? Again thank you for your help. "curtev" wrote: Ron, that works great. Thank you all for your help and patience. I am confused, however. Using Excel's formula of =RIGHT(YEAR(G1),2)&TEXT(G1-DATE(YEAR(G1),1,0),"000") on date format of 3/20/1996, it returns 96080 But using a converter on the Internet, and the first two formulas above, I get 96079. Why is that? Which is correct? Thank you very much for your help. "Ron Coderre" wrote: With A1: (a number representing YYYYMMDD) Try this: =MID(A1,3,2)&MID(1000+TEXT(A1,"0000\/00\/00")-("12/31/"&(LEFT(A1,4)-1)),2,3) Does that help? *********** Regards, Ron XL2002, WinXP "curtev" wrote: That almost did the trick. I need the two digit year in front as in: 3/20/1996 to 96080 Thanks much "Roger Govier" wrote: Hi Try =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))-DATE(LEFT(A1,4),1,1) -- Regards Roger Govier "curtev" wrote in message ... Greetings, I have dates in text, 19960320 (yyyymmdd) that need to be converted to Julian date. Can this be done without first converting to date format? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| to convert a julian date back to regular date | Excel Worksheet Functions | |||
| Convert a julian gregorian date code into a regular date | Excel Worksheet Functions | |||
| How do I convert a julian date to a regular date? | Excel Worksheet Functions | |||
| how to convert julian date to regular calendar date | Excel Worksheet Functions | |||
| convert julian date to gregorian date | Excel Discussion (Misc queries) | |||
Linear Mode
