OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Conditional formatting formula

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jgn
OOo Enthusiast
OOo Enthusiast


Joined: 17 Jul 2006
Posts: 130

PostPosted: Fri Jul 27, 2007 3:55 am    Post subject: Conditional formatting formula Reply with quote

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
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Fri Jul 27, 2007 5:17 am    Post subject: Reply with quote

There might be a more elegant way, but:
Code:
OR(YEAR(A1)<>YEAR(B1);MONTH(A1)<>MONTH(B1);DAY(A1)<>DAY(B1))
works.
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


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

PostPosted: Fri Jul 27, 2007 5:19 am    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Fri Jul 27, 2007 5:42 am    Post subject: Reply with quote

Code:
INT(A1-B1)
Tidy!
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


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

PostPosted: Fri Jul 27, 2007 6:39 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group