Leap Year and Julian Date formulas

[TiggerToo]

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

 

[Mark W.]

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

 

[Mark W.]

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