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...
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?
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)
= NETWORKDAYS(C2,D2) - NETWORKDAYS(C2,C2) - NETWORKDAYS(D2,D2) + 1 + MOD(D2,1) - MOD(C2,1)