# How to check if month in column B is before current month

1. ## How to check if month in column B is before current month

Hello all,
i'm trying to find a way (conditional formatting or auto macro while opening the excel) to check if typed month in column B is before current month. If so, cell next to it in column C turns red and shows a certain word.
If it is the current month, it turns green with another word.

2. ## Re: How to check if month in column B is before current month

=IF(Month(A1)<Month(Today()),"Past","Current") adjust A1 to cell containing typed date.

Select the formula cell, assuming B1... go to Format|Conditional Formatting

Cell Value Is >> Equal to >> ="Past" click Format, choose Red

Cell Value Is >> Equal to >> ="Current" click Format, choose Green,

Click Ok, Click Ok.

Change phrases as desired.

3. ## Re: How to check if month in column B is before current month

For the cell in C, =IF(OR(Month(Today())<Month(B2),Year(Today())<Year(B2)),"Green word","Red word")
For conditional formatting, just =OR(Month(Today())<Month(B2),Year(Today())<Year(B2))

4. ## Re: How to check if month in column B is before current month

Hmmmm.... NBVC, that'll only work as long as the month in question is within the same year. Come January, and your formula will produce a wrong result for comparison with a date in December that just passed.

In 2007 you can also use one of the conditional formatting date comparison options with "Dates occurring" and "Previous month", although I found that these also don't handle year breaks correctly, see here.

Darkyam's approach works, and another suggestion for a conditional formula FormulaIs would be

=MONTH(B2)=IF(MONTH(TODAY())=1,13,MONTH(TODAY()))-1

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1