Results 1 to 3 of 3
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    972
    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,472
    Thanks
    34
    Thanked 197 Times in 177 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

    "The trouble with quotes on the internet is that you can never know if they are genuine."
    Abraham Lincoln
    

  3. #3
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    972
    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
  •