time value format

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Select B1
Use next formula for Conditional Format
=IF(B1>$A$1,TRUE,FALSE)
Copy B1 and special paste to B1:B1OO
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
??? 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:
Upvote 0
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 ???
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top