Adding work days

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
1,001
Office Version
  1. 365
Platform
  1. Windows
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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))))
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
Ron & Dannyh1

Thank you for your responses.

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
 
Upvote 0
Ron's suggestion still works, just needs to be started on the previous day, i.e.

=WORKDAY(A2-1,B2*5)
 
Upvote 0
jasonb75

Thank you for having a look for me.

Unfortunately I still get Monday 5th December 2011 rather than Friday 2nd December
 
Upvote 0
strange - I definatley get 2/11 using Ron's formula - have you got your calculation on automatic?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top