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