Last month AND this month

[rickblunt]

Hello, on one of my inspection records an auditor has asked for me to give them an indication if the inspections are current or not. For the sake of simplicity I have set up a sheet that captures the dates of the last inspections. (An inspection is considered current if it was done "last month"). But we have all month to do it so it is not missed unless we do not do it this month at all

I have built this formula that works fine to let me know if we missed an inspection last month. But the issue is that as soon as we do an inspection for this month (the current one) it will default back to "MISSED". In this case G12 contains my max date, and the formula resides in G13

How can I tweak the formula so that it will look at matching the max date to last month AND this month?

=IF(MONTH(TODAY())=MAX(MONTH(G12))+1,"CURRENT","MISSED")

I appreciate any help at all - thanks

 

[jtakw]

Hi,

Try this:

=IF(MONTH(TODAY())<=MONTH(G12)+1,"CURRENT","MISSED")

 

[rickblunt]

Hi,

Try this:

=IF(MONTH(TODAY())<=MONTH(G12)+1,"CURRENT","MISSED")

WOW that was fast! I was so close too, I was trying to put a range in there, but not getting it correct. I was put my <>'s in the wrong spot.... Thank you so much for the help and the knowledge

 

[jtakw]

You're very welcome, thanks for the feedback.

 

[jtakw]

@rickblunt

I took another look at this, my post above was basically "fixing" the formula you posted, but after a 2nd look, I realize that formula will Error (wrong results), if you're crossing over year.
If your Last inspection was done in December, simply adding 1 to the month (which will result in 13) will be wrong, so use this formula instead.

=IF(MONTH(TODAY())<=MONTH(EDATE(G12,1)),"CURRENT","MISSED")

 

[rickblunt]

wow - gr8 catch, I had not thought of that. Thanks for going the extra effort on that - it is really appreciated. (Also gets me thinking that I need to review a few of my other formulas lol) many thanks again

 

[jtakw]

Sorry, just realized, even the modified formula is wrong, will post a fixed version in a little bit.
Busy at the moment.
Stay tuned.

 

[jtakw]

Ok, use this modified version, fully tested and works correctly:

=IF(EOMONTH(TODAY(),0)<=EOMONTH(EDATE(H12,1),0),"CURRENT","MISSED")

 

[rickblunt]

Ok, use this modified version, fully tested and works correctly:

=IF(EOMONTH(TODAY(),0)<=EOMONTH(EDATE(H12,1),0),"CURRENT","MISSED")

Thanks jtakw, I think I understand the addition of the "0", though it will take me a bit to wrap my head around it - lol. Thanks again for the assistance, and for teaching me more about the nuances of excel.

 

[jtakw]

You're very welcome.

The 0 brings the Dates to the End of the same month using EOMONTH.