NETWORKDAYS formula

Question

Hi, hoping someone can help&nbsp;I have two dates which I need to calculate the difference between the two date and time fields, I also need to exclude weekends.&nbsp;&nbsp;Start Date format dd/mm/yyyy hh:mmEnd date format dd/mm/yyyy hh:mm&nbsp;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.&nbsp;Any advice would be most welcome

mtarler · Answer

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)

patrick2788 · Answer

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.

mtarler · Answer

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

mtarler · Answer

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)

russnobby · Answer

Patrick2788&nbsp;&nbsp;Here's an example data set(Column C / Row 2) Call Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (Column D / Row 2) Response date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2022/1/11 20:56&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2022/1/12 0:14&nbsp;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.

Forum Discussion

NETWORKDAYS formula

6 Replies

Resources