Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Current w/c date
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!
Comments
-
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
-
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…!
-
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
-
Sorry - just tried putting some spaces in and now it says #UNPARSEABLE.
-
Ha ha! I'll try formatting...
-
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)
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I think I gave up on this one entirely, I don't even remember what I wanted to use it for!!!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives