Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Determine time elapsed based on working hours and working days only

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
brilliantidea
Asked:
brilliantidea
1 Solution
 
barry houdiniCommented:
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
 
brilliantideaAuthor Commented:
Thanks for your prompt reply.  It worked perfectly.  Thanks Barry.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now