Leap year determination

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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 you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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
Joined
Aug 31, 2005
Messages
5,828
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.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Probably easier......

=DAY(EOMONTH(A1,0))

Good point :)
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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...
 

Forum statistics

Threads
1,136,347
Messages
5,675,233
Members
419,555
Latest member
Paddington

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

Which adblocker are you using?

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
Top