Conditional formatting formula

jgn · OOo Enthusiast Joined: 17 Jul 2006 Posts: 130

Hi,
i have two cells A1 B1 both have Date and time. now what i want to do is when ever the date is diffrent from Cell A1 cell colour in B1 should change. i can do this through conditional formatting but the problem i am facing is if the date is same but the time is diffrent then also the colour of cell B1 gets change. i need that conditional formatiting checks only date and not time in the cell

Thanks in advacnce

huwg · Super User Joined: 14 Feb 2007 Posts: 890

There might be a more elegant way, but:

acknak · Posted: Fri Jul 27, 2007 5:19 am Post subject:

Dates are stored as integer values counting days. Times are stored as fractional values between 0 and 1.0 days. So a date with a time is simply the date integer plus the fraction of a day for the time.

So, your conditional formula to compare only the dates of your two cells will compare the integer parts of the two values, something like this: INT(A1)<>INT(B1), or even simpler: INT(A1-B1) (this will be non-zero, i.e. true, if the two dates are different)

huwg · Super User Joined: 14 Feb 2007 Posts: 890

acknak · Posted: Fri Jul 27, 2007 6:39 am Post subject:

Tidy, yes, but it's the kind of thing where the next poor soul who has to work on it looks and says "???". Usually, I'm the poor soul trying to figure out what I was thinking 6 months ago.

This is a pretty standard hack, but a spreadsheet newbie could really benefit from a small clue here. Your approach is a little more clear what's going on, and probably better from a maintenance standpoint.

In a program, I would add a comment, but in a spreadsheet formula, I don't know how to do that.

This works: