Results 1 to 3 of 3
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts

    Julian day in formula

    I have this formula:

    =AVERAGE(OFFSET($H$1,MATCH(TODAY(),$A:$A,FALSE)-30,0,5,1))

    The '30' represents 30 days in this formula.

    I want to replace this '30' number with a dynamic calculation that resolves to the Julian DAY number. So for today (July 29) that number would be 211. Tomorrow, it would be 212.

    I've been reading help on Julian dates in Excel but can't seem to find something that does this in a simple manner.

  2. #2
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,414
    Thanks
    33
    Thanked 195 Times in 175 Posts
    Quote Originally Posted by ibe98765 View Post
    I've been reading help on Julian dates in Excel but can't seem to find something that does this in a simple manner.
    Perhaps look at Ordinal date or days rather than Julian, but you won't find something particularly simple.
    The Wikipedia article is as good as any!
    BATcher

    Time prevents everything happening all at once...

  3. #3
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Lots of people have asked this question and similar (per Mr. Google). I finally stumbled on a concise answer:

    =TODAY()-DATE(YEAR(TODAY()),1,0)

Posting Permissions

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