Assistance on Formula for conditional cell formatting

[mrcooper]

Using EXCEL 2013

My challenge is to compare two cells, each containing a custom time value. The second cell will turn red if the time it contains is more than 15 minutes from the first. Both times were created to display a 24 hour format and allows the "Control" user to put a 4 digit value in without using a colon, (00\:00).

Personnel are allowed three breaks during their 8-hourshift: A Morning break for 15 minutes, a Lunch break for 30 minutes and another 15 minutes in the afternoon. These are indicated on the log sheet as three separate events, each event having to rows for time entry (start and end).

Personnel must notify the control officer (by radio), who logs the time they start their breaks and again when the they conclude their breaks. Of course, we have certain employees that stretch beyond their allotted time, and because the organization is under a strict time schedule those that extend beyond the allowed time effect the amount of time other personnel can have for their breaks. Supervisors want to document the employees and identify those who consistently extend their break times.

I have tried numerous formulas to include simply calculating the difference in both times of the cells (P2-O2) to find elapsed time; Isolating the hours and minutes of the times and finding the difference in time, (elapsed time of the minuets); using a greater than function, comparing the two cell values and adding 15 to P2, but have yet been unsuccessful. Often my formula works until I must calculate the elapsed time between something like 11:58 and 12:15. In my formulas Excel only recognizes that the in 11:58 is larger than the :15 in 12:15. I understand how to conditionally format the cell to turn (red with yellow text), but I can't find a fail-safe formula to make Excel realize that the time in P2 is greater than the 15 minutes allowed from the start time in O2.
Would anyone like to clue me in to the proper formula?

Thanks in advance - Coop

[mehmetcik]

With Dates and Times you really need to provide a sample.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mrcooper]

Yes Sir,

I hope this is more to the point. Using Excel 2013

Conditional formatting in cell "P2". I want cell "P2" to turn red if it is greater than 15 minutes than the time in Cell "O2" and remains unchanged if time is 15 minutes or less.

Example-1: Time in Cell "O2" = 11:58, Time in Cell "P2" = 12:14. Time in Cell "P2" is 15 minutes > than time of "O2" so the cell turns red.

Example-2: Time in Cell "O2" = 11:58, Time in Cell "P2" = 12:13. Time in Cell "P2" is = 15 minutes of time of "O2", cell does not turn red.

Example-3: Time in Cell "O2" = 11:58, Time in Cell "P2" = 12:10. Time in Cell "P2" is < time of "O2", cell does not turn red.

[Pepe Le Mokko]

Please attach a sample sheet as requested. Thank you

[mrcooper]

As requested. Conditional formatting would apply to columns D, F and H based on > than 15 minute difference between columns
C:D, E:F, G:H

[mehmetcik]

Select Columnd D, F anfd H

Set the conditional formatting rule to

=(HOUR(D1)*60+MINUTE(D1))-(HOUR(C1)*60+MINUTE(C1))>15



It is not practical for you manager to type in the times

Try this Instead:

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

Simply selecting a cell will enter the time

[mrcooper]

thank you, sir...appreciate the code!