Calculating average days, hours, minutes excluding weekend and holiday

[Ok-Feature-4243]

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!!

[#2]

@Ok-Feature-4243 

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.

[#3]

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.

[#4]

I found the answer from this thread: https://techcommunity.microsoft.com/t5/excel/calculating-months-days-hours-minutes-seconds-in-excel/...

Wondering if there is also a way to calculate the average excluding weekend and holiday?

[#5]

@Ok-Feature-4243 

You may calculate difference between each pair of days using WORKDAY.INTL function (microsoft.com), after that take average of all such differences.

[#6]

@Sergei Baklan 

My understanding, workday.intl function is to find the date, after a certain days from the starting date.

That said, I should know the number of days already, I just don't know when the ending date and workday.intl can tell me when the ending date in this scenario.

In my case, I have start date and ending date, what I do not know is the number of days (and hours) between the two dates.

The solution on the thread that I mentioned on my previous message is the solution that is works for me. It can calculate the number of days and hours and I can get the average as well. The problem is, I just realized that I also need to exclude weekend and holidays as well. Any chance you know the answer to this?