SOLVED

# IF Statement with Networkdays

Copper Contributor

# 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?

10 Replies

# Re: IF Statement with Networkdays

@Gumzz212 That could something like this:

``````=NETWORKDAYS(A1,A2)-1

or:

=NETWORKDAYS(A1+1,A2)``````

# Re: IF Statement with Networkdays

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

# Re: IF Statement with Networkdays

@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.

# Re: IF Statement with Networkdays

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

# Re: IF Statement with Networkdays

To exclude the first day from the count, I would go for @Riny_van_Eekelen 's first formula

``= NETWORKDAYS(initialDate+1, finalDate)``

# Re: IF Statement with Networkdays

@Gumzz212 See if the attached file contains the solution you seek.

# Re: IF Statement with Networkdays

yes, but how we make the date before deadline is the minus one
best response confirmed by Gumzz212 (Copper Contributor)
Solution

# Re: IF Statement with Networkdays

``=-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.

# Re: IF Statement with Networkdays

alternative would this work?
=NETWORKDAYS(A2,B2)+(2*(A2>B2)-1)*NETWORKDAYS(A2,A2)

# Re: IF Statement with Networkdays

thanks a lot for your help
1 best response

Accepted Solutions
best response confirmed by Gumzz212 (Copper Contributor)
Solution

# Re: IF Statement with Networkdays

``=-IF(A2>B2,NETWORKDAYS(A2,B2)+1,AND(A2<>B2,WEEKDAY(A2,2)<6)*(NETWORKDAYS(A2,B2)-1))``