Month formula to include current date/year

[Newport Count]

Hi there,

I have two date fields (C2 & C3) which are manually entered (dd/mm/yyyy). I need a message to appear in another cell (E2), which depends on the specific dates entered into cells C2 & C3.

If the date in cell C2 is the same month as the current date, then E2 is blank. This works ok with the following formula in E2:
=IF(AND(MONTH(C2)=MONTH(TODAY()),YEAR(C2)=YEAR(TODAY())),"",.......)
...I had to add YEAR into the formula as otherwise the same month from a previous year was producing the 'blank' result.

If the date in cell C2 is a previous month to the current date, then the pop-up message in E2 should state 'date in previous month'
=IF(MONTH(C2)<=MONTH(TODAY())-1,"previous",.......)
...I am a bit stuck with getting this formula to work, again with the YEAR function because, currently, it is only looking at the month and not the whole date i.e. today is 9th March 2021 but it does not recognise April 2020 as being a previous month!

I know why the formula does this but I just cannot work out how to resolve the issue. I know it will be a simple one!

Many thanks,
Rich

 

[Toadstool]

Hi Newport Count,

Today is 9 March 2021. If I want to check if a date is in a previous month then I need to check it is less than 1 March 2021, and there's a couple of ways of getting that date, so let me give two:

1. =IF(C2<DATE(YEAR(TODAY()),MONTH(TODAY()),1),"Previous","Not Previous")

2. =IF(C2<EOMONTH(TODAY(),-1)+1,"Previous","Not Previous")

 

[Newport Count]

Hi Newport Count,

Today is 9 March 2021. If I want to check if a date is in a previous month then I need to check it is less than 1 March 2021, and there's a couple of ways of getting that date, so let me give two:

1. =IF(C2<DATE(YEAR(TODAY()),MONTH(TODAY()),1),"Previous","Not Previous")

2. =IF(C2<EOMONTH(TODAY(),-1)+1,"Previous","Not Previous")

Thank you Toadstool.

I knew I was overcomplicating things! I used the 2nd code you provided.