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

 

[PCL]

Here the new formula to take of your full requirement
in B1 for the Conditional Format enter: =IF((B1-INT(B1))>($A$1-INT($A$1)),TRUE,FALSE)
The copy B1 and special past in B1:B17
If A1 = 20:20 it works

 

[kelvin_9]

Here the new formula to take of your full requirement
in B1 for the Conditional Format enter: =IF((B1-INT(B1))>($A$1-INT($A$1)),TRUE,FALSE)
The copy B1 and special past in B1:B17
If A1 = 20:20 it works

thank you so much for your solution, appreciated PCL!
however i made a fault that what if it greater or equal, isnt it =IF((B1-INT(B1))>=($A$1-INT($A$1)),TRUE,FALSE)

 

[PCL]

if it greater or equal, isnt it >=

YES
Ouf!!! We got it
What pleasant night to come

 

[kelvin_9]

YES
Ouf!!! We got it
What pleasant night to come

what if i tell you not working, do you think you gonna kill me?
deeply sorry.

http://kel.ddns.net/f/a4c3596705/?raw=1

 

[Misca]

Change your conditional formatting formula to

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

Looks like the times with dates aren't exactly the same as times without the dates when you dive deep enough into the decimals.

 

[PCL]

OK there is some rounding issue with numbers when there "equal" but not entered in the same manner: date versus time
see next formula
=IF(TRUNC((B1-INT(B1)),3)>=TRUNC(($A$1-INT($A$1)),3),TRUE,FALSE)

 

[kelvin_9]

OK there is some rounding issue with numbers when there "equal" but not entered in the same manner: date versus time
see next formula
=IF(TRUNC((B1-INT(B1)),3)>=TRUNC(($A$1-INT($A$1)),3),TRUE,FALSE)

when the time is less than column B 1min(or maybe more), like 20:44, it colored.

 

[kelvin_9]

Change your conditional formatting formula to

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

Looks like the times with dates aren't exactly the same as times without the dates when you dive deep enough into the decimals.

seems not working, it' pops error when click ok

 

[Misca]

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).

 

[PCL]

when the time is less than column B 1min(or maybe more), like 20:44, it colored.No, there is no issue in my sample file ....!
Can you send yours ?