[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
jgn
OOo Enthusiast

Joined: 17 Jul 2006
Posts: 130

 Posted: Fri Jul 27, 2007 3:55 am    Post subject: Conditional formatting formula 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

Posted: Fri Jul 27, 2007 5:17 am    Post subject:

There might be a more elegant way, but:
 Code: OR(YEAR(A1)<>YEAR(B1);MONTH(A1)<>MONTH(B1);DAY(A1)<>DAY(B1))
works.
acknak
Moderator

Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

 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

Posted: Fri Jul 27, 2007 5:42 am    Post subject:

 Code: INT(A1-B1)
Tidy!
acknak
Moderator

Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

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:
 Code: INT(A1-B1); "Compare the dates; ignore any time difference"

but I don't know if that's a Good Way to do it.

Anyone have another way to add a comment to a formula?
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum