Date calculation for next week

LisaB:-)LisaB:-)
edited 12/09/19 in Using Smartsheet
08/30/19 Edited 12/09/19

Hi, I want to run a report that will return all rows for tasks that need to be worked on next week.  So that will include tasks where the start date is next week, or the end date is next week, or simply tasks that are ongoing through next week, with neither a start or end date occurring during the week.

I have a hunch this might include a conditionally formatted check-box, but not sure where to start with the calculation.  Any help will be very much appreciated.

Tks Lisa

Previous13

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You would use something like this...

     

    =IF(AND([End Date]@row >= low end of date range, [Start Date]@row <= high end of date range), 1)

    .

    Obviously you will want to swap out the underlined text for however you want to establish your date range low and high ends, but basically you want to set the formula to check the box if the end date is greater than or equal to the start of the week and the start date is less than or equal to the end of the week.

  • Thanks!  Unfortunately, that's not working for me - I might have the date syntax wrong.  I'm using:

     

    =IF(AND([email protected] >= 9 / 9 / 2019, [email protected] <= 13 / 9 / 2019), 1)

     

    Would be very grateful if you could advise.  

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You are correct in that your date syntax is incorrect.

     

    =IF(AND([email protected] >= DATE(2019, 9, 9)[email protected] <= DATE(2019, 9, 13)), 1)

  • This. Is. AMAZING!  Thank you so much.  Before asking on here, I did a lot of searching for a solution, and couldn't find anything which matched exactly what I wanted.  I was surprised about that, because surely everyone wants a report which tells them what's coming next week, and for the next two weeks, etc?

    Thanks again :-)

  • ...Thinking about it though, do you know of a way that the calculation would work without entering the dates?  Because I would have to do that every week to run these reports.  Maybe some kind of COUNT?

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

    Hi Lisa,

    You could add a helper column that for example, could check a box if it should be included in the report. The formula in the cell would look at which week it is today and look in the past and the future as needed.

    Would that work?

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    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.

  • Hi, thanks for replying.  Yes, I'm using a helper column, but I don't know what the formula in the cell should be.  It needs to look at the next 7 days - any ideas?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. To use a checkbox column to compare to today's date, we would use a formula similar to this...

     

    =IF(AND([email protected] >= TODAY(7)[email protected] <= TODAY(14)), 1)

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

    Happy to help!

    I saw that Paul answered already! That should do it!

    Let me know if I can help with anything else!

    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.

  • Hmm, that's not working.  My Start is 26/08/19 and my Finish is 09/09/2019.  Would that be because it's calculating on the dates, and not the time between the two dates?

    Really appreciate your help, thank you.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    That would be because 9/9 does not fall inside of the range. Today plus 7 is 10/9. The formula is looking for things that fall on or between 7 days from now and 14 days from now. Not necessarily next week, but between one and two weeks from today.

     

    If you are looking for specific weeks, you will need to take into account week numbers instead of days.

     

    =IF(AND(WEEKNUMBER([email protected]) >= WEEKNUMBER(TODAY() + 1), WEEKNUMBER([email protected]) <= WEEKNUMBER(TODAY()) + 2), 1)

  • Hi, thanks for this, it's definitely a move in the right direction.  I do need it to return everything that happens between 9 and 15 September, and then next week I'll need it to return everything that happens between 16 - 22 September, etc etc. How would the formula work using week numbers?  Would I have to enter week numbers manually?

    I really appreciate your continued support, thank you.

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

    Pauls formula will update every time the sheet is opened and look at the current week. No need to change anything.

    Make sense?

    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.

  • Hi, the problem is that I need it to report on Monday - Friday next week, every week, not today + 7 days (which is Wednesday - Wednesday).

    Many thanks, Lisa

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

    Paul's latest formula should do that.

    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.