0

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.

2 Answers 2

4

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.

4
  • I'm missing where you get the initial assumption of not wanting to go back more than one day.
    – fixer1234
    Dec 7, 2017 at 21:39
  • @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...... Dec 7, 2017 at 21:47
  • OK, very clever. +1
    – fixer1234
    Dec 7, 2017 at 21:59
  • Very shrewd, Barry. Dec 7, 2017 at 22:27
2

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().

2
  • 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)? Dec 7, 2017 at 21:19
  • @barry houdini, Yup. It appears so.
    – B540Glenn
    Dec 8, 2017 at 13:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .