Leap Year Calculation

[KGee]

I'm trying to determine if a year is a leap year based on the start of the month and we use the following date format: dd - mmm - yy.

For example, someone enters the start date in cells C5, D5 & E5. Whenever the starting month is February, I then need to determine if it's a leap year to calculate the end of month date for cells F5, G5, H5 & I5. How can I write a formula to perform the calculations for cells F - I.

C5 D5 E5 F5 G5 H5 I5
01 feb 08 true 29 feb 08
01 feb 09 false 28 feb 09

I found this post helpful, but couldn't get it to work using 'feb' as opposed to '02' for the month.
http://www.mrexcel.com/forum/showthread.php?t=307140

 

[Richard Schollar]

Hi

Use DateValue:

=DAY(DATEVALUE(29&D5&E5))=1

returns TRUE (for not a leap year) or False if it is one. You could rewrite this as:

=DAY(DATEVALUE(29&D5&E5))<>1

to return TRUE if it is a leap year

 

[Ron Coderre]

Not *exactly* sure what you're looking for....but

This formula returns TRUE if the date referenced in C5:E5 is in a leap year:

F5: =DAY(DATE(E8,2,29))=29

And this formula returns the last day number of the month referenced in C5:E5

G5: =MAX(DAY(("1-"&D5&"-"&E5)+{27,28,29,30}))

or...if you have the Analysis ToolPak add-in:

G5: =DAY(EOMONTH(C5&"-"&D5&"-"&E5,0))

Does that help?

 

[KGee]

Not *exactly* sure what you're looking for....but

We have a spreadsheet which is used to run a monthly report. They used to enter dates for both the 1st day of the month and last day of the month in the dd-mmm-yy format used by the external application. It now has inputs for the start of the month, but in reality the day is ignored and only the month and year are used. The end of month is being determined automatically for generating the report. Someone had it hard coded to "29" for February which is how we noticed the problem.

If the month = "feb", I've added code based on your suggestions to check for a leap year and use 28 or 29 where appropriate.

Thank you both for your feedback.