Conditional formatting based on time

[dljjackson]

I'm trying to conditionally format a row based on if the time in column R exceeds an hour. I calculated column R with formula M2-P2 but M and P are two columns with time in different formats. The calculated times in column R appear correct but aren't formatting correctly based on my conditional formatting formula where I'm attempting to fill the row red if R2>TIME(0,59,59). I've also tried R2>(1/24). With both formulas every line is filled even the lines that are obviously less than 1 hour. Images attached. Any assistance would be greatly appreciated.

 

[Psygrrl88]

for your conditional formatting formula try:
Format only cells that contain > Cell Value > Greater Than > =0.0415
As you can see in the below image - this allows you to highlight values over 0:59 minutes.

If you need to do something like this in the future it is easier to convert the time to a number and use this kind of formula overall.

Time Submitted	Timezone	Checked In	Checked Out	Time	Variance
5:29:11 PM	EDT	3:20 PM	5:25 PM	2:05	0:04
6:09:15 PM	EDT		6:08 PM		0:01
9:12:37 PM	EDT		8:13 PM		0:59
8:32:07 PM	EDT		7:05 PM		1:27
8:28:15 PM			7:28 PM		1:00

 

[dljjackson]

Hi,

I tried this and it didn’t work. 0.0415 is the same as when I used (1/24). I believe the issue is with the number formatting but the report is already in that format when I download it. I’ve tried changed the cell format of M and P but conditional formatting still isn’t assessing the values correctly

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

also can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ExceLoki]

try this

=0.0416550925925926

 

[dljjackson]

Sorry, can't use XL2BB on my work computer. I did notice when I try to convert column R to numbers, they are all extremely high because they are including the date in numeric form. The number after the decimal is the number I'm trying to assess with conditional formatting. How can I get rid of the date?

 

[Fluff]

What is the formula in col R?

 

[dljjackson]

Column R=M-P. I came up with a resolution using the TRUNC function to get everything after the decimal into a different column then using conditional formatting on that. Highlighting all rows where that new column >0.0415. That will work for me. Unless there is a simpler method suggested

 

[Fluff]

Ok, how about

=MOD(M2-P2,1)

this will just give the decimal portion.

 

[ExceLoki]

Book1
	A	B	C	D	E	F
1	Time Submitted	Timezone	Checked In	Checked Out	Time	Variance
2	5:29:11 PM	EDT	3:20:00 PM	5:25:00 PM	2:05:00 AM	0.00
3	6:09:15 PM	EDT		6:08:00 PM		0.00
4	9:12:37 PM	EDT		8:13:00 PM		0.04
5	8:32:07 PM	EDT		7:05:00 PM		0.06
6	8:28:15 PM			7:28:00 PM		0.04
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=A2-D2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F6	Cell Value	>0.0416550925925926	text	NO