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