Leap year determination

[Dan Wilson]

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

 

[Oaktree]

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]

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]

Probably easier......

=DAY(EOMONTH(A1,0))

 

[NBVC]

Probably easier......

=DAY(EOMONTH(A1,0))

Good point

 

[Dan Wilson]

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]

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