Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Leap Year and Julian Date formulas

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Macon, Georgia USA
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need and updated type formula for for Julian Dates, where I can type in a julian date and get the date plus the day of the week, or vice verse.
    Like 2060 would be Mar 1, 2002

    I also need an updated formula for Leap Years such as iif I put in a date at anytime of a year JUN 10, 1999 instead of telling me, No - Not a leap Year I would like for cell to tell me when is the next leap year.

    JUN 1999 the next cell would should 2000
    MAR 2002 would show 2004.



    [ This Message was edited by: TiggerToo on 2002-03-13 13:54 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you use for the Julian date for Mar 1, 2010? For Mar 1, 2110?

    Without an answer to the above questions here's a stab at your 1st request...

    =DATE(LEFT(A1,LEN(A1)-3),1,0)+RIGHT(A1,3)

    ...for a julian date value in cell A1. The cell containing the formula can be formatted as...

    ddd, m/d/yyyy

    [ This Message was edited by: Mark W. on 2002-03-13 16:27 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's a "brute force" array formula that satisfies your leap year request...

    {=MAX(IF(DAY(DATE(YEAR(A1)+{1,2,3,4},3,0))=29,YEAR(A1)+{1,2,3,4}))}

    ...for a date value in cell A1. There's probably a more elegant approach, but I'll have to think on it awhile.

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

    BTW, I've re-read your original request and still can decide if you always want to display the next leap year even if the date in cell A1 (3/2/2000) falls within a leap year. If you want 3/2/2000 to display 2000 instead of 2004 use this array formula instead...

    {=MAX(IF(DAY(DATE(YEAR(A1)+{0,1,2,3},3,0))=29,YEAR(A1)+{0,1,2,3}))}

    [ This Message was edited by: Mark W. on 2002-03-13 15:17 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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