# Thread: Calculate Julian date for current year.

1. ## Calculate Julian date for current year.

Hi all,

Iam using Excel 2010.

Cell M5 on my worksheet I have formatted to show the current day as 12 December 2011.
What formula do I use to display the Julian date in cell M6?

Thanks,
Albert

2. The below will give you the Julina Data 2011346

=TEXT(M5,"yyyy")&TEXT((M5-DATEVALUE("1/1/"&TEXT(M5,"yy"))+1),"000")

Regards,
Tom Duthie

3. ## The Following User Says Thank You to duthiet For This Useful Post:

abaas (2011-12-12)

4. According to the formula at: http://en.wikipedia.org/wiki/Julian_day#Julian_Date to convert from greogrian to julian date use:
Code:
`=DAY(M5)+INT((153*(MONTH(M5)+12*INT((14-MONTH(M5))/12)-3)+2)/5)+365*(YEAR(M5)+4800-INT((14-MONTH(M5))/12))+INT((YEAR(M5)+4800-INT((14-MONTH(M5))/12))/4)-INT((YEAR(M5)+4800-INT((14-MONTH(M5))/12))/100)+INT((YEAR(M5)+4800-INT((14-MONTH(M5))/12))/400)-32045`
StevePS: You may want to test this, but doing a few calcs =M5+2415019 should give the Julian date...

5. ## The Following User Says Thank You to sdckapr For This Useful Post:

abaas (2011-12-12)

6. Thanks Tom.
I have replaced "yyyy" with just "" and it now just shows 346.
That's what I was looking for.

Thanks,
Albert

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•