Construct a formula to track time spent in business hours only

1. Construct a formula to track time spent in business hours only

I am in need of some assistance. I need to form a calculation for my employer. The Help Desk is opened from 7:30am to 6:30pm. I am in charge of excel reports for email support to ensure we are meeting all SLAs. If an email comes in at 8:00am and we have not responded to this user until the next day at 9:15am, we need to track the actual time it took to respond. How can we construct a formula to take into consideration the 11 hour work day from 7:30am to 6:30pm.

This is what i have so far. Again, it does not take into consideration our business hours and actual time it took to respond.

2. Re: Need him with a formulate!!!!!!!!!

Welcome to the forum

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

3. Re: Need him with a formulate!!!!!!!!!

Can anyone help me?

4. Re: Construct a formula to track time spent in business hours only

This formula will calculate business hours assuming that the start and end times are always within the business hours

=(NETWORKDAYS(A2,B2)-1)*("7:30"-"18:30")+MOD(B2,1)-MOD(A2,1)

where A2 has start time/date and B2 end time/date

format result cell as [h]:mm

If the email might come in outside of work hours then change to this version

=(NETWORKDAYS(A2,B2)-1)*("7:30"-"18:30")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"7:30","18:30")

5. Re: Construct a formula to track time spent in business hours only

Thank you Daddylonglegs. I am trying to implement this into my spreadsheet and it is not working correctly. A calculation that is only 3 hours is coming out over 12 hours.

6. Re: Need him with a formulate!!!!!!!!!

I am sorry. I do not know how to 'PM' you.

7. Re: Construct a formula to track time spent in business hours only

Can you give me the details for a calculation that doesn't give what you want - what dates/times do you have, what result do you expect?

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1