Month formula to include current date/year

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
326
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,463
Office Version
  1. 2016
Platform
  1. Windows
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. Excel Formula:
    =IF(C2<DATE(YEAR(TODAY()),MONTH(TODAY()),1),"Previous","Not Previous")
  2. Excel Formula:
    =IF(C2<EOMONTH(TODAY(),-1)+1,"Previous","Not Previous")
 
Solution

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
326
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. Excel Formula:
    =IF(C2<DATE(YEAR(TODAY()),MONTH(TODAY()),1),"Previous","Not Previous")
  2. Excel Formula:
    =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.

(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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
Top