Solved

Determine time elapsed based on working hours and working days only

Posted on 2013-02-06
2
267 Views
Last Modified: 2013-02-06
I am attempting to build a formula that will take Start date and time (represented in the following formula as column E) and the end date and time (represented by column J) and give me the total time worked based on an SLA that only counts Monday through Friday from 08:00 to 17:00, and does not count Saturdays, Sundays, or holidays.
Here is the formula I have cobbled together from other examples found elsewhere:

=MEDIAN(8/24,MOD((J161),1),17/24)-MEDIAN(8/24,MOD((E161),1),17/24)+(NETWORKDAYS((E161),(J161),Holidays)-1)*1/2))

A list of holidays has been created and the range named as 'holidays' for simplicity.  The list of holiday dates the NETWORKDAYS formula uses are:
-Jan 1
-Feb 18
-Mar 29
-May 20
-Jul 1
-Sep 2
-Oct 14
-Nov 11
-Dec 25
-Dec 26

Examples of calculations that should be returned if this formula works correctly:

Start d/t: 2013/01/09 10:05:00
End d/t: 2013/01/09 13:15:00
Total time worked should be 3h 10m 0s, as the start date and end date were on a Wednesday, completed within work hours (08:00-17:00).

Start d/t: 2013/01/05 20:05:00
End d/t: 2013/01/06 09:10:00
Total time worked should be 0h 0m 0s, as the start and end were both on a weekend.

Start d/t: 2013/01/06 20:05:00
End d/t: 2013/01/07 09:10:00
Total time worked should be 1h 10m 0s, as the start date was a Sunday, and it completed after 08:00 on Monday morning.

Start d/t: 2013/01/18 08:55:00
End d/t: 2013/01/22 08:45:00
Total time worked should be 17h 50m 0s, as the start date was a Friday during the workday, and it completed Tuesday almost at the same time, but the after-work hours and weekend hours are not included.

Start d/t: 2013/02/15 12:00:00
End d/t: 2013/02/19 13:30:00
Total time worked should be 10h 30m 0s, as the start date was a Friday at midday, and completed on the following Tuesday afternoon, but Monday not be counted because it was a holiday.

Sometimes my formula works but often it doesn't and it's typically when the calculation spans multiple days that it doesn't.

Any assistance would be appreciated.
0
Comment
Question by:brilliantidea
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38861434
Try this formula to get elapsed work hours

=(NETWORKDAYS(E2,J2,Holidays)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(J2,J2,Holidays),MEDIAN(MOD(J2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(E2,E2,Holidays)*MOD(E2,1),"8:00","17:00")

format result cell as [hh]:mm:ss

see attached showing your examples and one other....

The formula works for any start/end times, even at weekend/holidays etc. as long as J2>=E2

regards, barry
business-hours.xls
0
 

Author Closing Comment

by:brilliantidea
ID: 38861702
Thanks for your prompt reply.  It worked perfectly.  Thanks Barry.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question