Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Determine time elapsed based on working hours and working days only

Posted on 2013-02-06
2
Medium Priority
?
275 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 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