Excel Conditional Formatting Does Not Work when date is added to a cell based upon a formula.

Question

There are many posts similar to mine in which a conditional format works when you compare two dates from two different cells. When one of those dates are the output of a formula, then it does not .  I was not able leverage any similar posts to figure out my specific issue.  In my case, I am comparing a date manually entered into a cell to a cell in which the date is an import from a MS Project File.

In the cell that imports the date from the MS project file, the formula is ='[EU MDR Timeline Summary Project Data Export.xlsx]Sheet1'!$C$34 .  If I don't format the cell which is in the General Format, the value that is imported as 44566.71 which using the following conditional format, I can see what cell E5 shows up as yellow as 44566.71 <> 01/05/21 in cell D5 which is a Date Format.

However, when I format E5 to a Date format, I would expect the conditional format to see these two cells as equal and not have E5 shaded in yellow. However as you can see it still recognizes these two cells as not being equal.  Any other thoughts?

Answer 1

The .71 in the value corresponds to a time. If you format the cell as a date+time, you'll see

01/05/22 05:02 PM

Because of that time component, it is not equal to 01/05/22. You could either change the formula in E5 to

=INT(...!$C$34)

to get rid of the time, or change the conditional formatting formula to

=INT($E$5)<>$D$5

Answer 2

Hi Shooter71,

Have you referred to the reply provided by advisor above? Please let us know if you still need any further help.

Best Regards

Waqas Muhammad

Answer 3

Thank you HansV - that was indeed the ticket to fixing my issue.