time value format

kelvin_9

Board Regular
Joined
Mar 6, 2015
Messages
238
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Select B1
Use next formula for Conditional Format
=IF(B1>$A$1,TRUE,FALSE)
Copy B1 and special paste to B1:B1OO
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,559
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

Board Regular
Joined
Mar 6, 2015
Messages
238
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

Board Regular
Joined
Mar 6, 2015
Messages
238
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

Well-known Member
Joined
Aug 12, 2009
Messages
1,559
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.
 
Last edited:

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
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

Board Regular
Joined
Mar 6, 2015
Messages
238
??? 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.
 
Last edited:

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
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

Board Regular
Joined
Mar 6, 2015
Messages
238
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
 

Forum statistics

Threads
1,086,097
Messages
5,387,812
Members
402,081
Latest member
PiotrX

Some videos you may like

This Week's Hot Topics

Top