Forum Discussion
IF Statement with Networkdays
I'm trying to calculate workdays between two dates, but can we make it not to count starting date
Example:
I'm trying to count the day between 22/08/2023 - 23/08/2023 but the result always 2 days because the starting date is counted
how to make the starting date is not counted?
Gumzz212 Well just put a minus sign in front of IF
=-IF(A2>B2,NETWORKDAYS(A2,B2)+1,AND(A2<>B2,WEEKDAY(A2,2)<6)*(NETWORKDAYS(A2,B2)-1))Had I known you real intentions from the start I probably would have taken a different approach, but this works as well.
- hey could you help me, i try to use the -1 but when the progress start and end at the same day it always resulted -1
Gumzz212 That may happen when that date is NOT a working day. Then you would have to check first if the start and end date are the same, and if that's the case and it's not a working day, then return 0, otherwise calculate working days with the -1.
But of course, that only needed if you want to (or need to) correct the default calculation to begin with. You determine yourself weather a start at 24-Oct and an end on 25-Oct should be counted as 1 or 2. If the job starts 8AM on day1 an finishes 6PM the next day, you have covered 2 working days. But when both start and finish at noon on both days you would probably want it to be counted as 1 working day. You decide.
Anyhow, the formula could then look something like this:
=AND(A1<>B1,WEEKDAY(A1,2)<6)*(NETWORKDAYS(A1,B1)-1)where A1 an B1 contain the start and end dates.
