Forum Discussion
Solved
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 b...
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.
I've tried your formula that's fixed the first issue
but another issue is appear
when I input the finished date right before the starting date it count as -3 when it's manually counted it should be just -1
May I tell you what i want?
There is a fixed date that cannot be changed, I want this date to be a benchmark for the next date
The next date is a date that can change, it would be better if it was filled in before the fixed date but it's okay if it was filled in after the fixed date but don't take too long
As example
The fixed date/deadline was: 18 oct 2023
The change date : either before 18 oct 2023 or after
If it filled at 17 oct 2023 the count is -1
if it filled at 18 oct 2023 the count is 0
if it filled at 19 oct 2023 the count is 1
of course the count is excluding weekend
appreciate if you can help me
but another issue is appear
when I input the finished date right before the starting date it count as -3 when it's manually counted it should be just -1
May I tell you what i want?
There is a fixed date that cannot be changed, I want this date to be a benchmark for the next date
The next date is a date that can change, it would be better if it was filled in before the fixed date but it's okay if it was filled in after the fixed date but don't take too long
As example
The fixed date/deadline was: 18 oct 2023
The change date : either before 18 oct 2023 or after
If it filled at 17 oct 2023 the count is -1
if it filled at 18 oct 2023 the count is 0
if it filled at 19 oct 2023 the count is 1
of course the count is excluding weekend
appreciate if you can help me
Gumzz212 See if the attached file contains the solution you seek.
- yes, but how we make the date before deadline is the minus one
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.
- thanks a lot for your help