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.