# Date calculation for next week

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

«13

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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

=IF(AND(Finish@row >= 9 / 9 / 2019, Start@row <= 13 / 9 / 2019), 1)

Would be very grateful if you could advise.

• ✭✭✭✭✭✭
Options

You are correct in that your date syntax is incorrect.

=IF(AND(Finish@row >= DATE(2019, 9, 9)Start@row <= DATE(2019, 9, 13)), 1)

• ✭✭
Options

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 :-)

• ✭✭
Options

...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?

• ✭✭✭✭✭✭
Options

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 EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

=IF(AND(Finish@row >= TODAY(7)Start@row <= TODAY(14)), 1)

• ✭✭✭✭✭✭
Options

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 EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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(Finish@row) >= WEEKNUMBER(TODAY() + 1), WEEKNUMBER(Start@row) <= WEEKNUMBER(TODAY()) + 2), 1)

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Make sense?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

Paul's latest formula should do that.

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.