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

Options
Kirstine
Kirstine ✭✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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! 

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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
    Kirstine ✭✭✭✭✭✭
    edited 05/09/17
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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
    Kirstine ✭✭✭✭✭✭
    Options

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

  • Kirstine
    Kirstine ✭✭✭✭✭✭
    Options

    Ha ha! I'll try formatting...

  • Adam Overton
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Kirstine ✭✭✭✭✭✭
    Options

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

    laugh

This discussion has been closed.