# Conditional Formatting for an IF formula that includes time (H:mm)

#### lucy61176

##### New Member
I am wondering if there is a way to have conditional formatting for E10.

E10 is the total hours:minutes an employee is sick; formula is =SUM(E11:E9999). The format for column E is [h]:mm.

N4 is the percentage of the employee's position allocation, where 100% is equivalent to full time (formatted as a percent with no decimal places). N5 has a formula: =N4*64. 64 is the threshold for full-time employees (64 hours in 12 months). So N5 will calculate the sick hours' threshold for employees working less than full time.

I would like conditional formatting for E10 when the total of E10 is greater than N5. I do not know how to write the conditional formatting formula. Any help is greatly appreciated!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Select E10

Conditional Formatting
New Rule
Use a formula to determine...

=(E10 > N5)
format as required

Use Format painter (paintbrush icon) to copy to other cells if required.

If I understand you correctly, the following should be accurate...

Since Excel treats Time as a fractional representation of days and there are 24 hours in a day:

Set the conditional format of E10 to > N5/24

e.g.

the value 1 is equivalent to 1 day shown in [h]:mm format as 24:00
the value 2.5 is equivalent to 2 days and 12 hours show in [h]:mm format as 60:00

EDIT: I just realized that you aren't comparing days to hours, but simply hours to hours, the previous post should do what you are looking for.

Last edited:
Unfortunately, this is not working. I think it has to do with the fact that N5 is not formatted in time but E10 is as [h]:mm.

Last edited:
However, when I changed it to =E10>N5/24 it works! Thank you for your help.

LOL, I guess I was wrong in thinking that I was wrong.

I have a macro set up so that when the value of E10 is greater than N5/24, a message pops up. See old macro below. How do I revise it now so that it will pop up whenever E10 > N5/24. I tried putting that in and it doesn't seem to be working.

If Range("E10").Value > 2.6666666666667 Then
MsgBox "Employee has reached the threshold for sick leave. If employee provides verification and has and uses sick leave, the time over the threshold is approved, and the calculator at the top right should be used. Contact your HR Coordinator if employee does not provide verification."

Instead of literally typing 2.66...667 you may be better off using
Code:
``Range("N5").Value/24``
and let VBA handle the significance of the digits. (at least that way it will truncate in the same place)
Outside of that I don't see anything wrong with the portion of the code you posted. Perhaps if you can provide more of your code and elaborate on what exactly isn't working we can assist further.

This worked! I just don't know how to write code, so I did it wrong. THANK YOU THANK YOU!

Replies
2
Views
1K
Replies
12
Views
447
Replies
1
Views
222
Replies
3
Views
422
Replies
23
Views
2K

1,219,696
Messages
6,149,778
Members
450,913
Latest member
mdsuther

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

### Which adblocker are you using?

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

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