Forum Discussion
IF Statement with Networkdays
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.
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.
- 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 meGumzz212 See if the attached file contains the solution you seek.
- yes, but how we make the date before deadline is the minus one
