# NETWORKDAYS formula

Copper Contributor

# NETWORKDAYS formula

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

# Re: NETWORKDAYS formula

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)

# Re: NETWORKDAYS formula

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.

# Re: NETWORKDAYS formula

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.

# Re: NETWORKDAYS formula

= 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.

# Re: NETWORKDAYS formula

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?

# Re: NETWORKDAYS formula

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)