Problem in taking the weekof as Wednesday
I want Wednesday as the weekof based on the date
for e.g. If cell A has date of 25th Nov, the weekof in cell B for that date should be 20th Nov
If cell A has date of 27th Nov, the weekof in cell B for that should be 27th Nov
If cell A has date of 26th Nov, the weekof in cell B for that should be 20th Nov
What would be the formula to get this value?
I tried this formula
=IF(ISDATE([Planned Date]2), IF(WEEKDAY([Planned Date]2) = 3, [Planned Date]2  6, [Planned Date]2  WEEKDAY([Planned Date]2) + 4))
It works for all dates except for Monday
So, if cell A has value of 25th Nov, the weekof for that date is shown as 27th Nov, whereas it should take value of last week as weekof (20th Nov)
If cell A has value of 26th Nov, it is calculating correct data and the date is shown as 20th Nov
Comments

Match Index
1 4
2 5
3 6
4 7
5 6
6 5
7 4
.
Set up a table similar to the above (column headers in bold. Then use a formula similar to this...
=[Planned Date]@row + INDEX(Index:Index, MATCH(WEEKDAY([Planned Date]@row), Match:Match, 0))
.
What this does is takes the date then adds the appropriate number based on the WEEKDAY from the Index column. I use this method very frequently to populate Monday/Friday based on Start/Finish dates. I didn't test the numbers themselves, so you may need to tweak the Index column values, but I do know the formula itself works.
Help Article Resources
Categories
Check out the Formula Handbook template!