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

J. Craig Williams
J. Craig Williams ✭✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

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

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    edited 08/24/16

    Does that get it?

     

    Capture.PNG

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

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

     

    Craig

  • John Sauber
    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
    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
    John Sauber ✭✭✭✭✭✭
    edited 08/29/16

    @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
    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
    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
    J. Craig Williams ✭✭✭✭✭✭
    edited 09/20/16

    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.