Problem in taking the weekof as Wednesday

dishank.jain112376 · 11/24/19

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 · 11/25/19

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.

Problem in taking the weekof as Wednesday

Comments

Help Article Resources

Categories