Calculate overtime in Excel
Hi All,
I need to work out if the time captured in my AmendedDate column falls outside normal business hours.
So basically I am reporting for a Call Centre, which logs support tickets. In my Database I already have a created Date (when the Ticket was Issued), AmendedDate (When the technician made any changes) and CompletedDate (Ticket is closed). I would now like a formula that I can track whether the ticket is amended or closed if after 5pm.
So if the ticket is issued at 16:30, and the consultant either amends or closes the ticket at 17:30, I would like to track that 30min was spent on "overtime" for this technician. Same would apply if changes are made at 07:30 in the morning and the business day only start from 08:00 AM.
Any thoughts please?
Cheers
Software/Hardware used:
Microsoft Excel 2010
Software/Hardware used:
Microsoft Excel 2010
ASKED:
January 22, 2013 9:20 AM
UPDATED:
January 22, 2013 2:44 PM
Answer Wiki:
The overtime calculation for after 17:00 could be someting like =if(hour(CompletedDate)+minute(CompletedDate)/60-17>0,hour(CompletedDate)+minute(CompletedDate)/60-17,). This should compute how much time he completed after 5pm or return a blank if not after 5pm. The morning overtime is a little confusing to me. If he completed at 7:30, how much overtime did he have?
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
