how to remove saturday and sunday when calculating start dates

[VISHAL120]

Hi Everyone,

Am actually working with a lot of data where i need to calculate targets based on the previous events. The problem when the dates are being calculated it taking saturday and sunday also which i don't want as all the events shall happen on the week days.

i have been using this formula till now : =IF(C5="","",IF(TEXT(C5+5,"DDD")="SAT",C5+7,C5+5)) but the problem am unable to add the if statement for SUN as am getting error.

i would appreciate if i can get some help here either by doing it with VBA, User Defined fucntion ( as there are almost 3000 rows to analyse) or by formula itself. i have try weekdays, networkdays but did not get the results till now.

Am attaching a sample of the data here below.

thanks as usual for the kind help.

[Tom Jones]

Hi,

If I understand your question:

=IF(C5="","",IF(WEEKDAY(C5+5)=1,C5+6,IF(WEEKDAY(C5+5)=7,C5+7,C5+5)))


or maybe

=IF(C5="","",IF(WEEKDAY(C5)=1,C5+2,IF(WEEKDAY(C5)=7,C5+1,C5)))

[p45cal]

In cell C6:
=WORKDAY(C5,1)
copy down.

In cell E5:
=IF(C5="","",WORKDAY(C5,5))
copy down.

In cell G5:
=IF(E5="","",IF(F5="",WORKDAY(E5,PPS_Sending_Date),IF(F5<>"",WORKDAY(F5,PPS _Sending_Date))))
copy down

In cell I5:
=IF(G5="","",IF(H5="",WORKDAY(G5,Ok_Prod_To_Rec),IF(H5<>"",WORKDAY(H5,Ok_Pr od_To_Rec))))
copy down.

In cell K5:
=IF(I5="","",IF(J5="",WORKDAY(I5,Pre_Run_End_Date),IF(J5<>"",WORKDAY(J5,Pre _Run_End_Date))))
copy down.

In cell M5:
=IF(K5="","",IF(L5="",WORKDAY(K5,OF_Date),IF(L5<>"",WORKDAY(L5,OF_Date))))
copy down.

There is a Workday.Intl version too, which allows you to tell the formula what days of the week are working days.

[VISHAL120]

Hi Tom Jones and P45cal,

thank you both. Am trying the method shown by P45cal and will let you know te outcome after placing them in the main file. P45Cal can you give an example of the Workday.Intl as i have tried but did not get the results as everytime am getting errors. suppose the data in In cell G5:
=IF(E5="","",IF(F5="",WORKDAY(E5,PPS_Sending_Date),IF(F5<>"",WORKDAY(F5,PPS _Sending_Date)))) it considered Saturday as an approve date how do you assign it.

Many thanks by the way brothers.

[p45cal]

For Sat & Sun weekend (1 in the formula below):
In cell C5:
=WORKDAY.INTL(C5,1,1)

An example of one of the others, for cell I5:
=IF(G5="","",IF(H5="",WORKDAY.INTL(G5,Ok_Prod_To_Rec,1),IF(H5<>"",WORKDAY.I NTL(H5,Ok_Pr od_To_Rec,1))))

[VISHAL120]

Hi P45Cal, thank you very much for the quick reply, what i wanted to say was suppose for cell I5 saturday is accepted as a normal day and its only sunday which is not accepted and shall be +1 or +2 depending on the last working days. how do you assign it please sir.

[p45cal]

=IF(G5="","",IF(H5="",WORKDAY.INTL(G5,Ok_Prod_To_Rec,11),IF(H5<>"",WORKDAY.I NTL(H5,Ok_Pr od_To_Rec,11))))

[VISHAL120]

Hi P45Cal,

Thank you very much its works very well. The only thing now i see is even if the dates are week days like Thu-25-Apr its keeps addding 1 day ahead that is it makes it Fri-26-Apr. can we omit that as otherwise it make all analyse false and do not keep the flow of follow up.

P45Cal : as usual you have been a great help on all my work project. thank you for all sir. Even to the whole community without this community i would have been where i am today. thank you all.

[p45cal]

Which formula?
Where is it?
What do you want the result to be?
Why?

[VISHAL120]

Am attaching the file below as you can see on M7 the date shall have been Tue-23-Apr but it showing Thu-25-Apr and also i have used the formula workday only in cell S5 still it keep adding 1 more day that is instead of Mon-25-03 it shows Tue-26-03.See the attached file for better understanding.

is the formula am using for M7 is =IF(K7="","",IF(L7="",WORKDAY.INTL(K7+OF_Date,1),IF(L7<>"",WORKDAY.INTL(L7+ OF_Date,1))))
and the formula am using in S5 is =WORKDAY(R5,1)
all these are in sheet 3

thanks again for helping.

[p45cal]

I suspect you aren't using the function properly; the likes of your:
WORKDAY.INTL(K7+OF_Date,1)
might have to be:
WORKDAY.INTL(K7,OF_Date,1)
Look at the help for the function:
https://support.office.com/en-us/art...rs=en-US&ad=US

[VISHAL120]

Hi,

this is the formula are using : =IF(K7="","",IF(L7="",WORKDAY.INTL(K7,OF_Date,1),IF(L7<>"",WORKDAY.INTL(L7, OF_Date,1))))

and =WORKDAY(R5,1).

[p45cal]

Glad you got it working!

[VISHAL120]

thanks you P45Cal.