Date calculation based on weekend scenerios

[sjha]

Need to calculate ‘Install’ days based on scenarios. I have lists of ‘Training’ Days and ‘Install’ days should be 5 calendars prior to ‘Training’ days.

(1) if ‘Install’ date falls on Sunday then show next Monday date. i.e. My ‘Training’ date is 8/8/2009, and ‘Install’ date is 8/2/2009 but this date falls on Sunday so show 8/3/2009 which is Monday date.

(2) if ‘Install’ date falls on Saturday then show Friday date. i.e. My ‘Training’ date is 8/7/2009, and ‘Install’ date is 8/1/2009 but this date falls on Saturday so show 7/31/2009 which is Friday date.

Your help is much appreciated. Thank you.

 

[jdc]

Just to be quick, try using the WEEKDAY function and an IF statement using the Return type to either add a day or subtract.

Hope this helps

 

[sjha]

Thank you...but can you please elaborate? Thanks!

 

[sjha]

nevermind...I got it now. Thanks for your help.

 

[sjha]

Actually still having an issue. I have two parts formula and I need to put them together so that this becomes one formula.

Results should be based on either or:

First formula:=IF(WEEKDAY(X5)=7,X5-1,X5)
Second formula:=IF(WEEKDAY(X5)=1,X5+1,X5)

Thank you.

 

[sjha]

Any help on this, please?

 

[barry houdini]

Hello sjha,

You say that the install day should be 5 days before training day but your examples show 6 so, with training day in A1 get install day with this formula

=A1-CHOOSE(WEEKDAY(A1),6,6,6,6,6,7,5)