using conditional formatting to match MONTH

**greyscale** · 05-22-2011, 07:52 PM

Hi,

I have a cell that is populated using the following formula:
=IF(F280>("1/"&MONTH(A280)+1&"/"&YEAR(A280))-1,("1/"&MONTH(A280)+1&"/"&YEAR(A280))-1,F280)
it returns a date in format dd/mm/yyyy

I want the above cell to highlight red if the result of this formula does not match the month & year in the corresponding row in column A which is always the first day of the month, e.g. 01/mm/yyyy

I am using Excel 2003. Can anyone assist me with what I should be putting in conditional formatting to make this work?

Thanks,

**TMS** · 05-22-2011, 07:54 PM

Please post a sample workbook so we have some test data to work with.

Regards

**Cutter** · 05-22-2011, 09:39 PM

You can try this:

=DATE(YEAR(B280),MONTH(B280),1)<>A280
or maybe
=AND(COUNT(A280:B280)=2,DATE(YEAR(B280),MONTH(B280),1)<>A280)

For your cell formula you can be using a simpler construct:

=IF(F280>("1/"&MONTH(A280)+1&"/"&YEAR(A280))-1,("1/"&MONTH(A280)+1&"/"&YEAR(A280))-1,F280)
can be
=IF(F280>DATE(YEAR(A280),MONTH(A280)+1,1)-1,DATE(YEAR(A280),MONTH(A280)+1,1)-1,F280)

which, I believe, is simplified as:

=MAX(DATE(YEAR(A280),MONTH(A280)+1,1)-1,F280)

but without that sample file to test.....?????

**greyscale** · 05-22-2011, 10:12 PM

Thanks. Here is the sample file. I will have no control over people entering data in the columns that are not highlighted. There is a danger they may leave a blank cell or use the wrong format. i would like to place a conditional format in column H that would highlight problems like the one at H20 which is caused by a blank cell in column E. It would also check for any other errors that may occur with people copying and pasting cells and possibly knocking out the formulas. This is to give the users an immediate notification to rectify their entry.

I will try the example you have given above.

Thanks

**greyscale** · 05-22-2011, 10:23 PM

=IF(F280>DATE(YEAR(A280),MONTH(A280)+1,1)-1,DATE(YEAR(A280),MONTH(A280)+1,1)-1,F280)
works as a simplified calc and

=DATE(YEAR(H280),MONTH(H280),1)<>A280 works beautifully for the formatting.

Thanks for both your help.

**Cutter** · 05-23-2011, 08:14 AM

You're welcome.

The simplified formula I suggested should have been MIN() instead of MAX():

=MIN(DATE(YEAR(A280),MONTH(A280)+1,1)-1,F280)

If you have the Analysis Tool Pack installed you can use the EOMONTH() function:

=MIN(EOMONTH(A280,0),F280)