NETWORKDAYS formula

Copper Contributor

Hi, hoping someone can help

 

I have two dates which I need to calculate the difference between the two date and time fields, I also need to exclude weekends. 

 

Start Date format dd/mm/yyyy hh:mm

End date format dd/mm/yyyy hh:mm

 

I have tried using NETWORKDAYS but I need the output to be in HH:MM format not whole days. I've tried a number of suggestions via web searches but they don't work.

 

Any advice would be most welcome

6 Replies
so, you want actual NETWORKTIME meaning you want to exclude weekends and holidays AND know number of HOURS & MIN. I think you should be able to:
= NETWORKDAYS(...) - 1 + TIME([end]) - TIME([start])
That said, this does NOT take into account WORK HOURS (e.g. 9:00am - 5:00pm)
I think this is do-able. There's the question of where do the minutes come from? Are there partial and/or over time days to consider? If there's a start/end for every day in the period that would help.

@Patrick2788 

 

Here's an example data set
(Column C / Row 2) Call Date                    (Column D / Row 2) Response date
                                   2022/1/11 20:56                                            2022/1/12 0:14

 

What I need to do is calculate the difference between the two dates excluding weekends with the output data to be [h]:mm:ss as I have 600 rows of information which I need to average out.

= NETWORKDAYS(C2,D2) - 1 + MOD(D2,1) - MOD(C2,1)
and format cells as [h]:mm:ss as you noted.
but again this doesn't take into account any work hours and doesn't account for any start or end dates that are on the weekend.
Hi, thank you

What I'm finding is weekend dates are displayed as the dreaded ##### which if I'm correct in my thinking is caused by the addition of -1 in the centre of the formula? Is there a better way of calculating the information?
no that is probably caused by the cell being too small to hold the data. It might be trying to display a Date instead of a number. The problem with the -1 is that if the start or end date is already a weekend then the NETWORKDAYS won't count it and the -1 takes it away so the result will be 1 less than it should. If the start or end days might be a weekend I guess you could use:
= NETWORKDAYS(C2,D2) - NETWORKDAYS(C2,C2) - NETWORKDAYS(D2,D2) + 1 + MOD(D2,1) - MOD(C2,1)