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

[lucy61176]

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!

 

[Special-K99]

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.

 

[BiocideJ]

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.

 

[lucy61176]

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.

 

[lucy61176]

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

 

[BiocideJ]

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

Glad it worked even if I had doubted myself.

 

[lucy61176]

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

 

[BiocideJ]

Instead of literally typing 2.66...667 you may be better off using

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.

 

[lucy61176]

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