Current w/c date

Kirstine

I need a formula that will return the date of Monday for the current week.

So on Monday 08/05/2017 it will show 08/05/2017, but on Wednesday 10/05/2017 it will still show 08/05/2017. 

Please help! 

J. Craig Williams

Do you want Sunday 2017-05-07 to show 2017-05-08 or 2017-05-01?

If the first (Sunday is "this week")

=TODAY() - WEEKDAY(TODAY()) + 2

and if the second (Sunday is "last week")

=IF(WEEKDAY(TODAY()) >= 2, TODAY() - WEEKDAY(TODAY()) + 2, TODAY() - 6)

Hope this helps.

Craig

Kirstine

Hi Craig, 

Thanks for your help. The second is closer to what I want and I thought I could manipulate it to get my answer, however when I paste it into my sheet it brings up '#INVALID COLUMN VALUE'. I tried pasting it into a completely new sheet (in case it's something I'm doing) but it's the same there too...! 

In case it helps with the solution, what I want is for the cell to always show the most recent Monday (in England we usually start the week on the Monday rather than the Sunday, sorry!). So these are the dates I would need reflected in the cell:

Sunday 2017-05-07 > Monday 2017-05-01

Monday 2017-05-08 > Monday 2017-05-08

Tuesday 2017-05-09 > Monday 2017-05-08

Wednesday 2017-05-10 > Monday 2017-05-08

Thursday 2017-05-11 > Monday 2017-05-08

Friday 2017-05-12 > Monday 2017-05-08

Saturday 2017-05-13 > Monday 2017-05-08

Sunday 2017-05-14 > Monday 2017-05-08

Monday 2017-05-15 > Monday 2017-05-15

Sorry to spell it out but I thought it might help me as much as you…!

J. Craig Williams

That is probably caused by the column type being something other than Date.

I'm in Germany this month. Nearly every day is a workday and what day the week starts does not matter much, but I understand your system.  

Craig

Kirstine

Sorry - just tried putting some spaces in and now it says #UNPARSEABLE.

Kirstine

Ha ha! I'll try formatting...

Adam Overton

Kirstine,

I think Craig said this already, but if you get #INVALID COLUMN VALUE, then you need to right click the column and choose Edit Column Properties, and set the column type to Date.

Then the formula...

=[Input Date]2 - WEEKDAY([Input Date]2) + 2

...will give you the Monday for that week (if your week starts on Sunday)

 

Paul Newcome

Were you able to find a solution to this? I have a way of doing this very thing without having to use a long nested IF statement to account for each day of the week. I use a table and an INDEX/MATCH set-up.

Kirstine

I think I gave up on this one entirely, I don't even remember what I wanted to use it for!!!