Date calculation for next week
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
Comments
-
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(Finish@row >= 9 / 9 / 2019, Start@row <= 13 / 9 / 2019), 1)
Would be very grateful if you could advise.
-
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)
-
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?
-
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.
-
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?
-
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)
-
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.
-
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.
-
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)
-
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.
-
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.
-
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
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives