Updating Formula to Account for Leap Years

[jessebh2003]

In my spreadsheet, I have many formulas that either count or average given a date range. Everything works great except for February.

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

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

 

[Fluff]

How about

=IFERROR(COUNTIFS(Table13[PhysicianType],"*"&$D$9&"*",Table13[SubmissionDate],">="&DATE(Year,Feb,1),Table13[SubmissionDate],"<"&DATE(Year,Mar,1)),0)

 

[steve the fish]

You can wrap your date with EOMONTH.

 

[jasonb75]

2 ways that comes to mind to get the last day of Feb.

DATE(Year,Mar,0)

EOMONTH(DATE(Year,Feb,1),0)

 

[jessebh2003]

How about

=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!!

 

[jessebh2003]

You can wrap your date with EOMONTH.

Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jessebh2003]

2 ways that comes to mind to get the last day of Feb.

DATE(Year,Mar,0)

EOMONTH(DATE(Year,Feb,1),0)

Thank you.

 

[jasonb75]

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.