#### lapta301

##### Well-known Member
I must be having a bad day thankfully its Saturday tomorrow.

I have a date in A2 7th Nov 2011
Column B is number weeks until next appointment - 4.
Column C is the answer Friday 2nd December if the normal result is a Saturday or Sunday then use Friday.

Hope that makes sense and as ever thanks for your help.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Dannyh1

##### Well-known Member
Hi

In your example (07/11) I get next appointment to be Mon 5th December:

However try this:

=IF(WEEKDAY(A1+(B1*7))=1,(A1+(B1*7)-2),IF(WEEKDAY(A1+(B1*7))=7,(A1+(B1*7))-1,(A5+(B1*7))))

#### Ron Coderre

##### MrExcel MVP
Try something like this.
With
A1: a date....7-Nov-2011
B1: number of weeks...4
This regular formula returns the workday that is that number of weeks in the future (incrementing to the next Monday if that date would be a weekend.
C1: =WORKDAY(A1,B1*5)

In the above example, the formula returns: 5-Dec-2011
a Monday, because the actual date would be on a weekend.

Does that help?

well played sir

#### jasonb75

##### Well-known Member
I get Monday 5th as well, but this formula will roll any weekend dates back to the friday as you asked.

=A2+B2*7-MAX(WEEKDAY(A2,2)-5,0)

#### lapta301

##### Well-known Member
Ron & Dannyh1

I may not have explained myself clearly for which I apologise.

As I mentioned, the answer needs to be the last date in the weekly cycle and if this happens to be a weekend then it needs to be the Friday before not the Monday following.

A few examples

Monday 17th October - 8 weeks period ends on Friday 9th December
Tuesday 18th October - 8 weeks period end on Monday 12th December
Wednesday 19th October - 8 weeks period end on Tuesday 13th December

#### jasonb75

##### Well-known Member
Ron's suggestion still works, just needs to be started on the previous day, i.e.

=WORKDAY(A2-1,B2*5)

#### lapta301

##### Well-known Member
jasonb75

Thank you for having a look for me.

Unfortunately I still get Monday 5th December 2011 rather than Friday 2nd December

#### Dannyh1

##### Well-known Member
strange - I definatley get 2/11 using Ron's formula - have you got your calculation on automatic?

#### lapta301

##### Well-known Member
jasonb75

That's the one.

I've had a play about with a monthful of dates and it works fine.

Thank you so much, so simple in the end.

Also thanks to Ron & Dannyh1

Replies
0
Views
178
Replies
0
Views
407
Replies
0
Views
132
Replies
3
Views
896
Replies
0
Views
541

1,195,617
Messages
6,010,728
Members
441,565
Latest member
menangterus556

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back