time value format

[kelvin_9]

hi all, i would like to ask how can i amend my issue.

A1 is used to enter a time to color B1:B100 by conditional formatting which is greater than A1
i've manual changed the format both are "hh:mm", while i always get stuck in column B with format "date time" like "8/10/2019 20:15", thus i cant get the answer.

thanks all

 

[kelvin_9]

I'm still using that book1 with link, can you see the updated version?
http://kel.ddns.net/f/a4c3596705/?raw=1
thanks

 

[kelvin_9]

Let me try later, thanks in advance

 

[PCL]

I don't see any issue in the file you sent ...!

 

[kelvin_9]

I don't see any issue in the file you sent ...!

how about this? 20:44 will also colored

 

[PCL]

But don't you ask to color cells when the time value is greater than the time value in A1 ...??

A1 is used to enter a time to color B1:B100 by conditional formatting which is greater than A1

Could show exactly the result you want!

 

[PCL]

If you don't want the cell in column B to be colored when value is the same as A1
use
=IF(TRUNC((B1-INT(B1)),3)>TRUNC(($A$1-INT($A$1)),3),TRUE,FALSE)

 

[kelvin_9]

Oopsie: Forgot to change the formula to US settings. I'm using the Finnish ( / European ?) version where the delimiter between the parameters is ";". Replace them with commas and it should work:

=ROUND($A$1-INT($A$1),11)<=ROUND($B1-INT($B1),11)


I'm using 11 decimals here. That's a fraction of a second so in most of the cases something like 5 or 6 would be close enough (=roughly one second).

i've tested this one, great like PCL, but i still dont get it, why it doesnt work even "=" added in the formula when i enter the time which is column B has it also
see B7, B12

 

[kelvin_9]

If you don't want the cell in column B to be colored when value is the same as A1
use
=IF(TRUNC((B1-INT(B1)),3)>TRUNC(($A$1-INT($A$1)),3),TRUE,FALSE)

i want it same when i enter the time in A1, so i use ">="
however, when A1 is lesser than B, still colored. confusing and mess you all, i'm so sorry
PS: B7, B12 still color if A1 is 20:29

 

[PCL]

I'm lost

however, when A1 is lesser than B, still colored. confusing and mess you all, i'm so sorry

Yes, because B will be colored when B'values are greated than A1

PS: B7, B12 still color if A1 is 20:29

Yes,
B7 = B12 = 20:30 which is greater than A1
Where is it wrong ???

 

[kelvin_9]

I'm lost

Yes, because B will be colored when B'values are greated than A1
[/COLOR]

Yes,
B7 = B12 = 20:30 which is greater than A1
Where is it wrong ???

i messed obviously is 20:31, not 20:29
i feel sorry, anyway thanks in advance PCL