Working backwards to find a date based on a period - excluding weekends

[Terry-J]

Hi,

Anybody have any idea how to find the following:

I want to work out the date based on a number of days but excluding the weekends.

So sat the number is 10 days - this is 10 days since an event.

Working back from - 2/11/18.
This would be the 19th Oct - excluding weekends.

Date Day Days
02-Nov fri
01-Nov thu 1
31-Oct wed 2
30-Oct tue 3
29-Oct mon 4
28-Oct sun
27-Oct sat
26-Oct fri 5
25-Oct thu 6
24-Oct wed 7
23-Oct tue 8
22-Oct mon 9
21-Oct sun
20-Oct sat
19-Oct fri 10

Terry

[Aardigspook]

If the event itself can only be Mon-Fri, then just subtract 14 days - there will always be two weekends in between. If the event could be on a Sat or Sun, then you need to subtract either 12 or 13 days instead, like this (event date in $A$2):

Please Login or Register  to view this content.

i.e. if event date is a Sat, subtract 12 calendar days; if it's a Sun, subtract 13; if it's not either (so it's Mon-Fri), subtract 14.

[Terry-J]

Thanks very much - works great.


Terry

[Aardigspook]

You're welcome.