Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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...
    Last edited by sdckapr; 2011-12-12 at 15:37.

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

    abaas (2011-12-12)

  6. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA - USA
    Posts
    21
    Thanks
    17
    Thanked 0 Times in 0 Posts
    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
  •