Forum Discussion
Calculating average days, hours, minutes excluding weekend and holiday
Hi all,
I have start time on Column A and end time on column E in yyyy-mm-dd h:mm format and I calculate the time difference between the two on Column G using formula =$E6-$A6 in d "days," h "hours," m "minutes" format. There are multiple entries on column A, E and G.
My question: how do I get the average on Column G where the result is showing in d "days," h "hours," m "minutes" format? I tried the Average formula but it didn't work. I also need to exclude weekend and holiday as well.
This is the example of column A, E and G.
| 2020-08-12 12:52 | 2020-08-13 17:00 | 1 days, 4 hours, 8 minutes |
Thx all!!
5 Replies
Not sure I catch the question. Format doesn't matter, it only defines how to show values to users. You may take average of such values and display it in any applicable format you wish.
- I found the answer from this thread: https://techcommunity.microsoft.com/t5/excel/calculating-months-days-hours-minutes-seconds-in-excel/m-p/1287209
Wondering if there is also a way to calculate the average excluding weekend and holiday?You may calculate difference between each pair of days using WORKDAY.INTL function (microsoft.com), after that take average of all such differences.
I add more info on my post, hopefully it helps.
So, there are multiple entries and I tried to get the average of the time difference using the same d "days," h "hours," m "minutes" format. I tried using Average formula but it didn't works.
