• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting with time

kinkart

kinkart

Member
Hello,

I have a problem with a conditional formatting in a spreadsheet I made for work.
In short, it calculate time for a meeting and display the local time based on the timezone of each participant.

What I have is this:
G7: typical working day start = 08:00:00 formatted as time
H11: adjusted time start of the meeting based on the date and time of the meeting = 08:00:00 formatted as time

for my conditionnal formatting, I have:
$H11>=$G$7 turns the cell green

It works when the time is above G7, but not when the time is exactly G7.

Am I doing something wrong?
 
Hi ,

Can you format both cells , G7 and H11 , as Number , with say 10 decimal places , and check whether they are exactly equal ?

Narayan
 
@Narayan: yes both have the value if I change their format to say general, both says 0.333333 etc...

@Somendra Misra: yes it works. But it doesn't work on mine lol I have a bunch a formulas, maybe that's something causing the problem. It's very weird.

It's quite a complex spreadsheet (for me at least) with a bunch of formulas I didn't know before (like TRUNC), but here's an extract if it helps troubleshooting it
 

Attachments

Hi ,

The problem is Excel's !

Excel represents decimal numbers in floating point format , and the precision is limited to 15 digits.

A date + time has both an integer part and a decimal part ; the integer part takes up 5 digits e.g. 25/12/2013 is the same as the number 41633. Since 5 digits are already taken up in representing the date component , what is left to represent the time component is only 10 digits.

Thus beyond the significant 10 digits , the remaining digits can be anything , and Excel will use zeros in those places. Thus if you enter 25/12/2013 08:00:00 in a cell , and change the format to Number with say 20 decimal places , you will see 41633.33333333330000000000 , where only 10 significant decimal digits ( 15 - 5 ) are stored , the remaining being zeros.

A time component on the other hand does not have an integer part , since 24 hours corresponds to the interval 0 through 1. Thus if you enter a time such as 08:00:00 in a cell , and change the format to Number with say 20 decimal places , you will see 0.33333333333333300000 where 15 significant digits are used.

Thus , comparing two cells , one having a date + time component , and the other having only a time component will never work. The only way you can make it work is not to compare two cells for equality ; check to see whether the difference between them is less than a tolerance value e.g. 1 second equals 0.00001 , which is 5 significant digits ; thus if you revise your formulae so that instead of comparing H11 to G7 for equality , you compare it to see whether it is within 1 second of G7 , as follows :

=AND($H11-$G$7>=-0.00001,-0.00001<=$I$7-$H11)

you should get the correct formatting.

Narayan
 
worked perfect, thank for the fix and taking the time to explain what went wrong!
(and glad to know that my formulaes were correct and the fault was on Excel lol!)
 
You must log in or register to reply here.
Back
Top