Report on today and next workday

J. Craig Williams

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

Find more posts tagged with

Formulas

Reports

Comments

Brett Evans

Does that get it?

J. Craig Williams

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

Craig

John Sauber

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.

Kenyon Bajus

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.

Screen Shot 2016-08-29 at 7.56.00 AM.png

John Sauber

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

Kenyon Bajus

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.

Jerry Tutt

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

J. Craig Williams

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

b) create report that showed them

both

Craig