Forum Discussion
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:m...
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.
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)
