| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert julian date
I have a julian format of YYDDD. The formula I'm using is off a day and a
century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
http://www.vertex42.com/ExcelTemplat...-calendar.html
-- Regards, Peo Sjoblom "JHL" wrote in message ... I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
Your formula is giving me 28 Feb 1905 (this is correct the 59th day of a
year is 28 Feb; 3 Mar is day 62 in a non-leap year) This =DATE(LEFT(A3,2)+100,1,(MID(A3,3,3))) gives me 28 Feb 2005 best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "JHL" wrote in message ... I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
On Nov 10, 7:26*pm, "Peo Sjoblom" wrote:
http://www.vertex42.com/ExcelTemplat...-calendar.html -- Regards, Peo Sjoblom "JHL" wrote in message ... I have a julian format of YYDDD. *The formula I'm using is off a day and a century. Formula *=date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance.- Hide quoted text - - Show quoted text - Hello JHL, Your formula has one parenthesis too many, I assume you are using this formula =DATE(LEFT(A1,2),1,MID(A1,3,3)) In which case I'm not sure how you get 3/31/1905, if I have 05059 in A1 I get 28th Feb 1905....so you only have to fix the year part as far as I can see. If all dates are this century try =DATE(LEFT(A1,2)+100,1,MID(A1,3,3)) regards, barry |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
The problem with 2-digit years is knowing when the century is 1900 or 2000.
Pick a cut-off year (say 60) and assume anything less than that is in the 2000 century and anything equal to or greater is in the 1900 century... =DATE(1900+100*(--LEFT(A1,2)<60)+LEFT(A1,2),1,(MID(A1,3,3))) -- Rick (MVP - Excel) "JHL" wrote in message ... I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
=DATE(2000+LEFT(A1,2),1,0)+RIGHT(A1,3)
-- Gary''s Student - gsnu200908 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
Ignore my previous post (wherever it went) I had an elderly moment, it's a
julian date i.e. day 59 of 2005, use this =DATE(IF((LEFT(A1,2)+0)<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3)) Mike "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
=DATE(20&LEFT(B29,2),1,RIGHT(B29,3))
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
Hi,
Why is 2/28/2005 the correct date for 05059? I can understand the first 05 being the year but how do we arrive at 28 Feb from the 059 bit? Mike "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
=DATE(LEFT(A1,2)+2000,1,RIGHT(A1,3))
"JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
hi
see this site http://www.cpearson.com/excel/jdates.htm regard FSt1 "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
Try =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
-- Kevin "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
-- Kevin "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3))
Source: http://www.cpearson.com/excel/jdates.htm -- Kevin "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
What date do you think the 59th day of the year is, Mike?
-- David Biddulph "Mike H" wrote in message ... Hi, Why is 2/28/2005 the correct date for 05059? I can understand the first 05 being the year but how do we arrive at 28 Feb from the 059 bit? Mike "JHL" wrote: I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance. |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert julian date
Thanks EVERYONE for the responses.
barry houdini, yours was the easiest to implement. "barry houdini" wrote: On Nov 10, 7:26 pm, "Peo Sjoblom" wrote: http://www.vertex42.com/ExcelTemplat...-calendar.html -- Regards, Peo Sjoblom "JHL" wrote in message ... I have a julian format of YYDDD. The formula I'm using is off a day and a century. Formula =date(left(a1,2),1,(mid(a1,3,3)) 05059 = 3/31/1905 How can I get the correct answer of 2/28/2005? Thanks in advance.- Hide quoted text - - Show quoted text - Hello JHL, Your formula has one parenthesis too many, I assume you are using this formula =DATE(LEFT(A1,2),1,MID(A1,3,3)) In which case I'm not sure how you get 3/31/1905, if I have 05059 in A1 I get 28th Feb 1905....so you only have to fix the year part as far as I can see. If all dates are this century try =DATE(LEFT(A1,2)+100,1,MID(A1,3,3)) regards, barry . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert Julian date to DD/MM/YYYY | Excel Discussion (Misc queries) | |||
| how to convert from julian date to mm/dd/year | Excel Discussion (Misc queries) | |||
| Convert a julian gregorian date code into a regular date | Excel Worksheet Functions | |||
| how to convert julian date to regular calendar date | Excel Worksheet Functions | |||
| convert Julian date | Excel Worksheet Functions | |||
Linear Mode
