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

Options
✭✭✭✭✭✭
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭
edited 08/24/16
Options

Does that get it?

• ✭✭✭✭✭✭
Options

No, because if today is Monday and the End Date is Wednesday, this will give a false positive.

Craig

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 08/29/16
Options

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

• ✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 09/20/16
Options

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

This discussion has been closed.