3

I have an Excel 2007 worksheet where I need to calculate the difference between 2 times, excluding weekends, then at the end of a week or month, be able to find the average of the amounts of time in the 'difference' column.

Here's what I have so far:

=NETWORKDAYS(A3,H3)-1&" DAYS, "&TEXT(H3-A3,"HH"" HRS AND ""MM""MIN"""))

Which gives me a result of 1 DAYS, 02 HRS AND 03MIN which is fine; I just can't figure out a way to find an average of the data in that column with the time expressed in this format.

Any suggestions?

Thanks much.

1 Answer 1

3

Your existing formula won't always work, e.g. if A3 is a Thursday at 10:00 and H3 is the following day at 09:00 shouldn't the result be

0 DAYS, 23 HRS AND 00MIN

but actual result with that formula is 1 day too high, i.e.

1 DAYS, 23 HRS AND 00MIN

Why not return the result in hh:mm and then you can average those, i.e. use this formula

=NETWORKDAYS(A3,H3)-1+MOD(H3,1)-MOD(A3,1)

custom format results as [h]:mm then you can simply average the result column

Note: the formula works assuming that A3 and/or H3 are always weekdays

Alternative solution:

Use this formula to fix the problem I identified

=NETWORKDAYS(A3,H3)-1-(MOD(H3,1)< MOD(A3,1))&" DAYS, "&TEXT(H3-A3,"HH"" HRS AND ""MM"" MIN""")

Assuming you have results using that formula in J3:J10 try this formula to give you an average in the same format

=SUMPRODUCT(MID(0&J3:J10,FIND({"D","H","M"},J3:J10)-2,3)/{1,24,1440})/ROWS(J3:J10)

format result cell as custom:

d" DAYS, "hh" HRS AND "mm" MIN"

Note: this works assuming that no period is longer than 99 working days and where average working days is < 32 days

11
  • I'm quite new to using Excel and its formulas, but doesn't the -1 in my original formula address the problem of the result being 1 day too high?
    – Jill
    Sep 27, 2013 at 13:16
  • Also, formatting the result as [h]:mm completely removes the days portion of the result - it just completely ignores the days. I wouldn't mind if the result is expressed as hours and minutes as long as it also included 24 hours for every day also.
    – Jill
    Sep 27, 2013 at 13:19
  • No because you will get the same number of days whether the time gap is 23:00 hours or 25:00 - I'll post a working version in my answer. To get hours over 24 you need to include the square brackets like [h]:mm not h:mm Sep 27, 2013 at 13:19
  • 2
    @Jill, to see what's wrong with your existing formula, put in 9/26/13 11:00pm and 9/27/2013 01:am. You should get 0 days and 2 hours, but instead you get 1 day and 2 hours, because =NETWORKDAYS() counts any portion of a workday as a full day.
    – Dane
    Sep 27, 2013 at 13:25
  • Ah! Thank you for the bracket help - that made all the difference in the world. I now get a result that includes the days. Thank you for working on posting a version that will work for me to get the correct number of days. Also, in answer to one of your comments above, we will not be entering any date information on weekends.
    – Jill
    Sep 27, 2013 at 13:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .