Calculate overtime in Excel

50 pts.
Tags:
Microsoft Excel 2010
Microsoft Excel formulas
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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?

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following