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
Bookmarks