# Leap Year Calculation

#### KGee

##### Well-known Member
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Richard Schollar

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

##### MrExcel MVP
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:
Code:
``F5: =DAY(DATE(E8,2,29))=29``

And this formula returns the last day number of the month referenced in C5:E5
Code:
``G5: =MAX(DAY(("1-"&D5&"-"&E5)+{27,28,29,30}))``
or...if you have the Analysis ToolPak add-in:
Code:
``G5: =DAY(EOMONTH(C5&"-"&D5&"-"&E5,0))``
Does that help?

#### KGee

##### Well-known Member
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.

Replies
3
Views
2K
Replies
3
Views
467
Replies
0
Views
216
Replies
1
Views
245
Replies
0
Views
200

Threads
1,195,934
Messages
6,012,386
Members
441,694
Latest member
Elvin A

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back