Leap Year and Julian Date formulas

TiggerToo · Mar 13th, 2002, 07:51 PM

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. · Mar 13th, 2002, 08:09 PM

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. · Mar 13th, 2002, 08:20 PM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 07:51 PM	#1
TiggerToo Join Date: Feb 2002 Location: Macon, Georgia USA Posts: 116	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 ]

Mar 13th, 2002, 08:09 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,653	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 ]

Mar 13th, 2002, 08:20 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,653	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 ]