Problem in taking the weekof as Wednesday

dishank.jain112376

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

Paul Newcome

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.