Excel, calculate +8 weeks to the nearest Wednesday (or Sunday)

We have a business process that requires us to calculate

(TODAY + 8 weeks) to the nearest Sunday or Wednesday.

For example
- Today is 07/12.
- 8 weeks is 01/02/18
- This is a Thursday. Count to the nearest Wednesday or Sunday in the past [1]. Count to the nearest Wednesday or Sunday in the future [3]
- Select day with the lowest number of hops between the calculated date. Therefore we select Wednesday 31/01/18
- If it is a Friday and there is a tie (-2 to Wednesday, +2 to Sunday) select the Sunday.

I'm happy with Excel to the point of =TODAY()+(8*7) but the next step to 'is this a Wednesday or Sunday, if not count forward and back until it is both then compare the two values and select the closest is well beyond me. I assume it needs some VB but this isn't something I am familiar with (although I would be able to do this in powershell however, I'm not incapable just unfamiliar).

Is anyone able to get me started?

Ultimately I'd like to embed the results into a Word Document but that can come later.

Patrick

Posted 2017-12-07T13:51:21.857

Reputation: 1 132

Answers

As you never want to go back more than one day (from the 56 initially added) ^[1], it would be sufficient to add 56, subtract 2 and then find the next occurrence of a Wednesday or a Sunday.

You can do that using WORKDAY.INTL function (Excel 2010 or later) like this:

=WORKDAY.INTL(TODAY()+8*7-2,1,"1101110")

WORKDAY.INTL can be customised to define the "working days" with a string like "1101110" - the string is of 7 1/0 values starting with Monday and ending with Sunday where 0 represents a working day and 1 a non-working day, so "1101110" defines Wednesday and Sunday as the only working days

In older versions of Excel you can get the same result with this formula

=MIN(TODAY()+8*7+6-WEEKDAY(TODAY()+8*7-{2,5}))

^[1] Explanation: the nearest Sunday or Wednesday can never be more than 2 days away in either direction and when date+56 = Friday Patrick says he wants to pick the Sunday.....so if date+56 is a Thursday or Monday then you need to go back 1, any other situation you either keep date+56 or go forwards.

barry houdini

Posted 2017-12-07T13:51:21.857

Reputation: 10 434

I'm missing where you get the initial assumption of not wanting to go back more than one day. – fixer1234 – 2017-12-07T21:39:22.950

@fixer1234, just deduced from the description - the nearest Sunday or Wednesday can never be more than 2 days away in either direction and when date+56 = Friday Patrick says he wants to pick the Sunday.....so if date+56 is a Thursday or Monday then you need to go back 1, any other situation you either keep date+56 or go forwards...... – barry houdini – 2017-12-07T21:47:36.413

OK, very clever. +1 – fixer1234 – 2017-12-07T21:59:21.133

Very shrewd, Barry. – Bandersnatch – 2017-12-07T22:27:36.530

How does this work for you? It should calculate the closest Sunday or Wednesday 8 weeks in the future.

To verify it works, Place a date in A2. You can change your reference from A2 to Today() after you are satisfied it works.

Place this formula in B2:

=(A2+(8*7)) - CHOOSE(WEEKDAY(A2+(8*7)),0,1,-1,0,1,-2,-1)

Place this formula in C2 to check the results:

=TEXT(B2,"DDD")

To test it, place your dates in A2 and check your calendar.
If your weekday does not start on Sunday, then you can adjust 4 in the condition test of the IF().

B540Glenn

Posted 2017-12-07T13:51:21.857

Reputation: 1 045

Wouldn't it be sufficient to use just the FALSE part of the IF function, i.e. just =A2+(8*7) - CHOOSE(WEEKDAY(A2+(8*7)),0,1,-1,0,1,-2,-1)? – barry houdini – 2017-12-07T21:19:27.783

@barry houdini, Yup. It appears so. – B540Glenn – 2017-12-08T13:56:10.807