# Leap year determination

#### Dan Wilson

##### Active Member
Good day. I am using Excel 2003 on Win XP Pro and consider myself to be an above average user. I have a workbook containing 32 worksheets. The first 31 worksheets relate to each day of the month. The last worksheet is a totals sheet showing totals for the month. The totals worksheet shows the total days in the month versus the total days my store was actually open in the month. I am currently using a table in a hidden area of the worksheet with one cell for each month and an adjacent cell for each month containing the number of days in that month. The number of days in the month is then accessed using a VLOOKUP function using the current month of the workbook. I have searched the message board for topics about leap year, but did not find what I am looking for.

What I want to know is if there is a way to detect the leap year in a cell formula so that the number of days in the month of February can be adjusted for a leap year? I seem to recall that many years ago I was able to create such a formula, but I can no longer find it.

Thanks for any help. Danno...

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Oaktree

##### MrExcel MVP
There are several ways you can do this. One way is:

=DAY(DATE(A1,3,0))=29 will return TRUE if the year (stored as a number, not an Excel date) in A1 is a leap year

#### NBVC

##### Well-known Member
If A1 contains a date within month of February including the year, then:

=EOMONTH(A1,0)-EOMONTH(A1,-1) will return the number of days in the month. So if it is a leap year, then it will return 29.

EOMONTH() is a Toolpak Addin function.

#### barry houdini

##### MrExcel MVP
Probably easier......

=DAY(EOMONTH(A1,0))

#### NBVC

##### Well-known Member
Probably easier......

=DAY(EOMONTH(A1,0))

Good point

#### Dan Wilson

##### Active Member
Leap Year help

Good day all. Thanks for the help on the leap year function. I will try both ways. I can always count on getting an answer here.

Thanks, Danno...

#### Dan Wilson

##### Active Member
Leap Year help

Good day NVBC and Barry.

It looks like the EOMONTH function is the winner. By inserting this function into the cell showing the Days in the Month, I have totally eliminated the need for the table of months. This one funtion looks at the first day of the month in the worksheet and works beautifully!

Thanks again, Danno...

Replies
20
Views
883
Replies
27
Views
495
Replies
18
Views
342
Replies
1
Views
416
Replies
6
Views
465

1,171,972
Messages
5,878,548
Members
433,351
Latest member
Cindy Little

### 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.

### Which adblocker are you using?

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

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