• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

End of month formula

Hi - As you will see from the attached spreadsheet, I am trying to add a column that will help me to calculate a rolling 12 month figure using the formula =AND(B7>=EOMONTH(TODAY(),-13)+1,B7<EOMONTH(TODAY(),-1)).

This seems to work apart from leap years and it doesn't seem to be picking up the 31/05/2020 as being part of May.

Am I being thick or can someone enlighten me in what I am doing wrong.

Thanks a lot.
 

Attachments

Chihiro

Excel Ninja
EOMONTH will accommodate for leap year.

I think your comparison operator is off... Should it not be... something like below?
I.E. Between 6/1/2019 to 5/31/2020.
=AND(B7>EOMONTH(TODAY(),-13)+1,B7<=EOMONTH(TODAY(),-1))

Your formula checks for Between 5/31/2019 to 5/30/2020.
 
Top