TiggerToo
Board Regular


Joined: 27 Feb 2002
Posts: 77
Location: Macon, Georgia USA
Flag:

Status: Offline

   

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.
MrExcel MVP


Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

   

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.
MrExcel MVP


Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

   

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 ]