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.
Report on today and next workday
I'd like a Report that shows rows with the Date field from today or the next workday.
That is on
Monday - shows Monday/Tuesday
Tuesday - shows Tuesday/Wednesday
...
Friday - shows Friday/Monday
Saturday - shows Saturday/Monday
Sunday - shows Sunday/Monday
My sheet is very complictated and starting to visibly slow down so avoiding additional columns and formulas is desired.
I have a solution by adding a column and formula, I'm just checking if I am missing something obvious.
Thanks.
Craig
Comments
-
Does that get it?
-
No, because if today is Monday and the End Date is Wednesday, this will give a false positive.
Craig
-
JC,
We do this in one of our major schedules. We use a binary column (checkbox, flag, star) and put this formula in there.
=OR(DateColumn1 = WORKDAY(TODAY(), 1), DateColumn1 = TODAY())
https://app.smartsheet.com/b/publish?EQBCT=1262fc277a7d4d86bdc51ab1ac21dc3e
You then report on that field checked or not.
We used to do this with an =IF(OR(This or That), 1, 0) to mark the column on or off, but I learned that with the new formulas, you can use functions that return true and false (like AND(), OR(), NOT(), etc.) to drive the formula directly without wrapping it in an IF() statement. This saves some computing resources in the back end and should be the best way to contribute the least amount of complexity when adding this feature.
-
I had a similar need. My report pulls from several sheets that all have DATE and END columns, DATE is start date and END is end date (obviously).
In my report, for WHEN, I use:
DATE
-is today or
-is in the next 1 day(s)
Works perfectly for my needs.
-
@Ken,
I think the only thing you'll have to watch out for is if you have non-work time in your sheet (such as weekends or holidays). If you had Saturday and Sunday as non-workdays, for instance, on Friday, that report will not bring up your Friday and Monday tasks, it will only show Friday and Saturday tasks.
-
Understood, but for my purposes, my set-up works since I need to see every day of the week. Every day is a work day.
-
Hmmmm..... I have a similar need but, with another twist....My durations include a 7-day work week. However, the end date cannot fall on a weekend or holiday. (ie: in the Legal Profession the court system and title companies do not work on weekends or holidays. However, their tasks are based on a 7-day week.) Fortunately for me our projects are small enough that I can carefully manipulate the durations. Unfortunately it is a manual effort. I have submitted an enhancement request for this.
Thanks,
jerry
-
Jerry,
The main problem you would run into is that if Dependencies are on, the End Date can't contain a formula (it already does, hidden from us based on duration, predessors, and start date).
What I would do in your case would be to add a row to determine if End Date was a Saturday or Sunday and then either
a) highlight the ones that needed to be manually changed
or
b) create report that showed them
or
both
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 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