Adding days in set increments

[techsan05]

Hi all,

Sorry for the lengthy description. I am a newbie here and thought I would include as much info as possible:

I am attempting to put together a schedule (list of dates at this point), where each widget will need to be serviced on a set interval of days from a start date. Therefore I could see all the upcoming service dates for a given widget. The problem: some of the service dates returned will fall on a weekend (Saturday or Sunday). The task would need to be performed on or prior to the due date. When this occurs I would like for the formula to recognize this. Thus the service due dates in these cases would need to fall on the Friday preceding the service due date in the upcoming weekend.

Service interval (frequency in days) in A2
Start date in B2
In C2: =B2+$A$2

I get the next service date by adding the frequency (B2) to the start date (A2). Subsequent dates are added in D2, etc by adding the date in the preceding cell (C2) to the service interval anchored in $B$2.

I thought that incorporating the WORKDAY function might make this fairly simple. However, I found that it ONLY counts the work days. Using this would project a due date much further in the future by omitting the days of the weekend.

From here I attempted to incorporate IF statements and WEEKDAY to try and identify Saturday's and Sunday's.

Any help or feedback is appreciated, Thanks in advance!

 

[asad]

Try

=IF(WEEKDAY(B2+A$2,2)>5,(B2+A$2+(5-WEEKDAY(B2+A$2,2))),B2+A$2)

 

[techsan05]

Wow, this works great!

Why "2" at the end of the weekday function and use of 5?

 

[asad]

Sorry, I was not online before.

Weekday function with "2" means that week is starting from Monday as day 1 through to Sunday as Day 7. Hence the use of 5 in formula to say if the weekday number is over 5, then it is either a Saturday or a Sunday and then I deducted enough days to get the Friday immediately before.