Home 
Search 
Today's Posts 
#1




convert Julian date to DD/MM/YYYY
Hi
I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#2




convert Julian date to DD/MM/YYYY
Look he
http://www.cpearson.com/excel/jdates.htm  Kind regards, Niek Otten Microsoft MVP  Excel "Raj" wrote in message ... Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#3




convert Julian date to DD/MM/YYYY
Hi neik
I tried the formula =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3)) I have the julian date as 108001 has to give 1/1/2008 but it is giving 1/1/2010 can you help me regarding this "Niek Otten" wrote: Look he http://www.cpearson.com/excel/jdates.htm  Kind regards, Niek Otten Microsoft MVP  Excel "Raj" wrote in message ... Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#4




convert Julian date to DD/MM/YYYY
108001 is not a Julian date as defined by Chip; it has 6 digits, not 5.
What is the meaning of the first 1? If you don't need it, drop it and use Chip's formula. Otherwise, tell us what the coding system of your dates is.  Kind regards, Niek Otten Microsoft MVP  Excel "Raj" wrote in message ... Hi neik I tried the formula =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3)) I have the julian date as 108001 has to give 1/1/2008 but it is giving 1/1/2010 can you help me regarding this "Niek Otten" wrote: Look he http://www.cpearson.com/excel/jdates.htm  Kind regards, Niek Otten Microsoft MVP  Excel "Raj" wrote in message ... Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#5




convert Julian date to DD/MM/YYYY
<what the coding system of your dates is
Look here to see how many interpretations of "Julian date"there a http://en.wikipedia.org/wiki/Julian_date  Kind regards, Niek Otten Microsoft MVP  Excel "Niek Otten" wrote in message ... 108001 is not a Julian date as defined by Chip; it has 6 digits, not 5. What is the meaning of the first 1? If you don't need it, drop it and use Chip's formula. Otherwise, tell us what the coding system of your dates is.  Kind regards, Niek Otten Microsoft MVP  Excel "Raj" wrote in message ... Hi neik I tried the formula =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3)) I have the julian date as 108001 has to give 1/1/2008 but it is giving 1/1/2010 can you help me regarding this "Niek Otten" wrote: Look he http://www.cpearson.com/excel/jdates.htm  Kind regards, Niek Otten Microsoft MVP  Excel "Raj" wrote in message ... Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#6




convert Julian date to DD/MM/YYYY
Hi,
Please explain the 108001 date to us so we can help figure out what you need. Where does the 80 come from? 10 is 2010? and 01 is 1? Cheers, Shane Devenshire "Raj" wrote in message ... Hi neik I tried the formula =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1 ,RIGHT(A1,3)) I have the julian date as 108001 has to give 1/1/2008 but it is giving 1/1/2010 can you help me regarding this "Niek Otten" wrote: Look he http://www.cpearson.com/excel/jdates.htm  Kind regards, Niek Otten Microsoft MVP  Excel "Raj" wrote in message ... Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#7




convert Julian date to DD/MM/YYYY
=DATE(INT(A1/1000)1,12,31)+((INT(A1/1000)*1000)+A1)
The above seems to work for me. Excel seems to assume the first three digits should be added to 1900. "Raj" wrote: Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#8




convert Julian date to DD/MM/YYYY
Was there any special reason for using
+((INT(A1/1000)*1000)+A1) rather than +MOD(A1,1000) ?  David Biddulph AnotherNewGuy wrote: =DATE(INT(A1/1000)1,12,31)+((INT(A1/1000)*1000)+A1) The above seems to work for me. Excel seems to assume the first three digits should be added to 1900. "Raj" wrote: Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
#9




convert Julian date to DD/MM/YYYY
Because I didn't think of using mod() ;o)
I worked this out two years ago to deal with Julian dates formatted like 2008001. I've learned a lot since then, primarily from this forum. It turned out the exact formula worked for dates formatted like 108001. thx for the suggestion. I'll change my example sheet. "David Biddulph" wrote: Was there any special reason for using +((INT(A1/1000)*1000)+A1) rather than +MOD(A1,1000) ?  David Biddulph AnotherNewGuy wrote: =DATE(INT(A1/1000)1,12,31)+((INT(A1/1000)*1000)+A1) The above seems to work for me. Excel seems to assume the first three digits should be added to 1900. "Raj" wrote: Hi I want to convert Julian Date to DD/MM/YYYY format is there any way we can do it in excel 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how to convert from julian date to mm/dd/year  Excel Discussion (Misc queries)  
convert date mm/dd/yyyy to dd/mm/yyyy  Excel Worksheet Functions  
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  
convert Julian date  Excel Worksheet Functions 