PDA

View Full Version : [SOLVED] how to remove saturday and sunday when calculating start dates



VISHAL120
03-29-2019, 11:07 PM
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
03-30-2019, 07:04 AM
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
03-30-2019, 08:12 AM
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_Prod_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
03-30-2019, 10:15 AM
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
03-30-2019, 11:03 AM
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.INTL(H5,Ok_Pr od_To_Rec,1))))

VISHAL120
03-30-2019, 11:21 AM
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
03-30-2019, 01:06 PM
=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
03-31-2019, 02:12 AM
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
03-31-2019, 04:17 AM
Which formula?
Where is it?
What do you want the result to be?
Why?

VISHAL120
03-31-2019, 08:01 AM
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
03-31-2019, 02:46 PM
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/article/workday-intl-function-a378391c-9ba7-4678-8a39-39611a9bf81d?NS=EXCEL&Version=90&SysLcid=1033&UiLcid=1033&AppVer=ZXL900&HelpId=xlmain11.chm60569&ui=en-US&rs=en-US&ad=US

VISHAL120
03-31-2019, 07:48 PM
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
04-01-2019, 08:20 AM
Glad you got it working!

VISHAL120
04-01-2019, 08:54 AM
thanks you P45Cal.