Aug 30 2023 11:52 PM
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?
Aug 31 2023 01:09 AM - edited Aug 31 2023 01:12 AM
Oct 25 2023 12:02 AM
Oct 25 2023 12:36 AM
@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.
Oct 25 2023 02:02 AM
Oct 25 2023 02:33 AM
To exclude the first day from the count, I would go for @Riny_van_Eekelen 's first formula
= NETWORKDAYS(initialDate+1, finalDate)
Oct 25 2023 02:36 AM
@Gumzz212 See if the attached file contains the solution you seek.
Oct 25 2023 02:56 AM
Oct 25 2023 03:14 AM - edited Oct 25 2023 03:56 AM
Solution@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.
Oct 25 2023 05:57 AM - edited Oct 25 2023 06:00 AM
alternative would this work?
=NETWORKDAYS(A2,B2)+(2*(A2>B2)-1)*NETWORKDAYS(A2,A2)
Oct 25 2023 03:14 AM - edited Oct 25 2023 03:56 AM
Solution@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.