Filter to show only dates from this week???

12/11/18 Edited 12/09/19

I'm making a project schedule for our construction company.

I would like to be able to have a filter that only shows "Start Date" that is equal to this week or this month.

Is that possible?

Previous1

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 12/11/18

    Hi,

    Yes, it is.

    Try this.

    Edit: Updated with a simpler solution.

    My example is for the current week, but you could set it up for the month as well.

    Ad a weekcheck column (Checkbox) and add the formula below.

    =IF(WEEKNUMBER([email protected]) = WEEKNUMBER(TODAY()); 1)    

    The same version but with the below changes for your and others convenience.    

    =IF(WEEKNUMBER([email protected]) = WEEKNUMBER(TODAY()), 1)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    To use the filter for the current week, you'd choose to filter by the Weekcheck column and where the checkbox is checked.

    Would that work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thank you.  Sorry I'm just not good when it comes to formulas.

     

    I created the new Checkbox column.  In that same column I put the formula below

    =IF(WEEKNUMBER([Start Date]1) = WEEKNUMBER(TODAY()), 1)

    Nothing seems to be happening.  Shouldn't the checkbox be "checked"?

    Capture.PNG

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 12/11/18

    Happy to help!

    It looks correct. Try to delete the last parenthesis so that Smartsheet can try to run and fix it automatically. And if that doesn't work, you could try to select the Start Date cell again.

    Did it work?

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • GOT IT! Thanks

    One more.  I have projects with columns for "Contract Date".  I was looking for a way to use conditionally formatting to make the row RED color is "Contract Date" older than 6 months so I could highlight old jobs. 

    Doesn't seem like you can do that with conditionally formatting alone.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Great!

    I'm always happy to help!

    Yes, you can. If you choose a date column in "Set Condition" you can select "is in the last (days)" and there you could set something like 180 days.

    Would that work?

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • That seems like it would work for dates from today back to 180 days.  But what about 180 or greater? Don't want to set condition with before 180 days.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    That will work as well with the "is in the next (days)" selection.

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • When I use

    "Is in the last" (days) and make it 180 days It's highlighting everything from today back 180 days.

    When I use

    "is in the next" (days) and make it 180 no condition are set because that's the future

    Can't get it to highlight dates that are greater than 180 days in the past

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    My mistake!

    You would probably need a helper column for that to work

    I'll get back to you if I come to think about anything else.

    Sorry about the confusion.

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You could use another checkbox column with 

     

    =IF([Contract Date]@row < TODAY(-180), 1)

     

    This will flag if it is older than 180 days. You could then base your conditional formatting off of that.

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Yes, of course. I was so focused on the conditional part that I missed that. 

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Perfect.  Thanks guys

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I do the same thing too sometimes. Lol

    thinkspi.com

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.