Conditional formatting based on time


New Member
Dec 21, 2014
Office Version
  1. 2013
  1. Windows
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.


  • Adjusted Time Submitted screenshot.PNG
    Adjusted Time Submitted screenshot.PNG
    37.2 KB · Views: 23
  • Checked Out screenshot.PNG
    Checked Out screenshot.PNG
    40 KB · Views: 22
  • Conditonal Formatting Rule.PNG
    Conditonal Formatting Rule.PNG
    45.4 KB · Views: 22

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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 SubmittedTimezoneChecked InChecked OutTimeVariance
5:29:11 PMEDT3:20 PM5:25 PM2:050:04
6:09:15 PMEDT6:08 PM0:01
9:12:37 PMEDT8:13 PM0:59
8:32:07 PMEDT7:05 PM1:27
8:28:15 PM7:28 PM1:00
Upvote 0

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
Upvote 0
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.
Upvote 0
try this

Upvote 0
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?



  • 1639504969290.png
    40.5 KB · Views: 8
Upvote 0
What is the formula in col R?
Upvote 0
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
Upvote 0
Ok, how about
Excel Formula:
this will just give the decimal portion.
Upvote 0
1Time SubmittedTimezoneChecked InChecked OutTimeVariance
25:29:11 PMEDT3:20:00 PM5:25:00 PM2:05:00 AM0.00
36:09:15 PMEDT6:08:00 PM0.00
49:12:37 PMEDT8:13:00 PM0.04
58:32:07 PMEDT7:05:00 PM0.06
68:28:15 PM7:28:00 PM0.04
Cell Formulas
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F6Cell Value>0.0416550925925926textNO
Upvote 0

Forum statistics

Latest member

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