convert Julian date to DD/MM/YYYY

R

Raj

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

Raj

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
 
N

Niek Otten

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.
 
S

Shane Devenshire

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
 
A

AnotherNewGuy

=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.
 
D

David Biddulph

Was there any special reason for using
+(-(INT(A1/1000)*1000)+A1)
rather than
+MOD(A1,1000) ?
 
A

AnotherNewGuy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top