Updating Formula to Account for Leap Years

jessebh2003

Board Regular
Joined
Feb 28, 2020
Messages
71
Office Version
  1. 365
Platform
  1. Windows
In my spreadsheet, I have many formulas that either count or average given a date range. Everything works great except for February.

Excel Formula:
=IFERROR(COUNTIFS(Table13[PhysicianType],"*"&$D$9&"*",Table13[SubmissionDate],">="&DATE(Year,Feb,1),Table13[SubmissionDate],"<="&DATE(Year,Feb,28)),0)
Excel Formula:
=IFERROR(AVERAGEIFS(Table13[TheContentWasRelevantToMyWork],Table13[SubmissionDate],">="&DATE(Year,Feb,1),Table13[SubmissionDate],"<="&DATE(Year,Feb,28)),0)

I've named some of my cells to make the formula easier for me to read.
  • Year - E1, which is a drop down for the year
  • Jan - E2, Feb - G2, Mar - I2, etc.
If the value of Year is a leap year, the formula will exclude any values for February 29th. I tried changing 28 to 29, but then values for March 1st were being included in non leap year years. I searched and searched online and the only thing I could find were formulas about how to determine if a year was a leap year with a True or False.

Is there a way to automatically update the 28 to a 29 if the year is a leap year or a another way to write the formulas? Appreciate any help or suggestions! Thanks.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
How about
Excel Formula:
=IFERROR(COUNTIFS(Table13[PhysicianType],"*"&$D$9&"*",Table13[SubmissionDate],">="&DATE(Year,Feb,1),Table13[SubmissionDate],"<"&DATE(Year,Mar,1)),0)
 
Upvote 0
Solution
2 ways that comes to mind to get the last day of Feb.

DATE(Year,Mar,0)

EOMONTH(DATE(Year,Feb,1),0)
 
Upvote 0
How about
Excel Formula:
=IFERROR(COUNTIFS(Table13[PhysicianType],"*"&$D$9&"*",Table13[SubmissionDate],">="&DATE(Year,Feb,1),Table13[SubmissionDate],"<"&DATE(Year,Mar,1)),0)
Oh wow! I can't believe I didn't even think about. Worked like a charm! Thank you!!
 
Upvote 0
If you want a consistent formula that you can use for any month rather than having Fluff's edit for Feb and your original formula for the rest of the year then I would suggest going with

<=EOMONTH(DATE(Year,Month,1),0)

With Fluff's syntax of <DATE(Year,Next Month,1) you would need to increase the year when going from Dec to Jan to prevent erroneous results, with EOMONTH this is not necessary.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top