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]

Select B1
Use next formula for Conditional Format
=IF(B1>$A$1,TRUE,FALSE)
Copy B1 and special paste to B1:B1OO

 

[Misca]

Try something like

=MOD($B1,INT($B1))<$A$1 ( or simply =($B1-INT($B1))<$A$1 )

The MOD returns the reminder ( = time ). It takes two parameters, number and divisor. INT returns only the whole number so I'm using it as the divisor. Now the MOD only returns the time value without the date part.

The second suggestion does exactly the same by subtracting the whole number from the decimal one.

 

[kelvin_9]

Select B1
Use next formula for Conditional Format
=IF(B1>$A$1,TRUE,FALSE)
Copy B1 and special paste to B1:B1OO

i tried this in conditional formatting, it doesn't work? time format issue?

 

[kelvin_9]

Try something like

=MOD($B1,INT($B1))<$A$1 ( or simply =($B1-INT($B1))<$A$1 )

The MOD returns the reminder ( = time ). It takes two parameters, number and divisor. INT returns only the whole number so I'm using it as the divisor. Now the MOD only returns the time value without the date part.

The second suggestion does exactly the same by subtracting the whole number from the decimal one.

which cell should i use this formula?

 

[Misca]

I understood you were looking for the formula to use with the conditional formatting.

But if you only want to get the time part of the datetime value to your worksheet use the =$B1-INT($B1) where you want to have the time.

The formulas expect there's just time value in A1 and the datetime in the B-column. If this is not the case you might want to use the same formula for A1 in the conditional formatting equation.

 

[PCL]

i tried this in conditional formatting, it doesn't work? time format issue?

??? it worked in my test file ...!
Did you follow the steps I mentioned ??
Can you give detailed example of your data.

 

[kelvin_9]

??? it worked in my test file ...!
Did you follow the steps I mentioned ??
Can you give detailed example of your data.[/COLOR]

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

i might have sth wrong i'm sorry
even A1 is empty, B1 still colored.

column B is lookup from database to return the last time of my employee clock out record
plus, 1.5hr earlier than column B will color means they have overtime allowance.

 

[PCL]

Some Comments
File sample is welcome ...!
If there is no value in A1 of course it cannot work, because we are comparing the time cell value to A1

conditional formatting which is greater than A1

You want to check time and not date ... !

So what is the value in A1, where the time value as reference ???

 

[kelvin_9]

Some Comments
File sample is welcome ...!
If there is no value in A1 of course it cannot work, because we are comparing the time cell value to A1


You want to check time and not date ... !

So what is the value in A1, where the time value as reference ???

time enter by me, even i enter 21:00, it's still not working fine
as i said, i have manual change both format cell as "hh:mm"

p.s. the file entered time 21:00 and tested again