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.

Conditional Formating between 2 dates

Jason S
Jason S ✭
edited 12/09/19 in Archived 2015 Posts

I know how to achieve this in excel but can't figure it out here.

 

We are looking to highlight a cell (or row) if the number of business days between two cells is less than 3 business days

 

For example in a row we have:

Date1 = 11/30/15

Date2 = 12/2/15

 

In this case above we would want to color Date2 red (or the whole row) since there are less than 3 business days between the 2 dates

 

If Date1 was 11/30 and Date2 was 12/3 then there would be no color highlight.

Tags:
· Share on FacebookShare on Twitter

Comments

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

    Jason,

     

    Add a third column and put your calculation ( [Date1]23 - [Date2]23 for row 23 ) there.

    This will be a number. Set your conditional formatting on this number.

     

    I do this with a column for the difference between the due date and today's date (which is (  =today()  ) and then assign a value to the urgency column

     

    Here's the formua from Smartsheet

     

    =IF(Done25, "", IF([DueDate-Today]25 < 0, 5, IF([DueDate-Today]25 = 0, 4, IF([DueDate-Today]25 <= 2, 3, IF([DueDate-Today]25 <= 5, 2, 1)))))

     

    and here it is again easier to read with explanations

     

    =IF(Done25, "",      -- if done, there is no urgency

      IF([DueDate-Today]25 < 0, 5,   -- if past due, 5

      IF([DueDate-Today]25 = 0, 4,  -- if due today, 4

      IF([DueDate-Today]25 <= 2, 3,  -- if due in the next two days, 3

      IF([DueDate-Today]25 <= 5, 2,  -- if due in the next five days, 2

      1)))))  -- otherwise 1

     

    and the conditional formats feed off the 1-5 values.

     

    A lot more complicated than your need, but hope this gives you want.

     

    Craig

    · Share on FacebookShare on Twitter
  • Spencer Simmonds
    Spencer Simmonds ✭

    Hi Craig

     

    Would you be able to assist with this one please?

     

    It is close to this query from Jason, but different.

     

    https://community.smartsheet.com/discussion/trying-validate-date-fields-some-way

     

    Kind regards

    Spencer

    · Share on FacebookShare on Twitter
  • Jason S
    Jason S ✭

    Craig, thanks for your response.  Can you think of a way make it calculate traditional business days plus saturday?

     

    We basically use a 6 day workweek that we need to base the calulation on - so normal workweek plus Saturday

     

    We need to set a 3 business rule between 2 dates

     

    So something we are trying to acheive having smartsheet calcualte a intial due date based on a final due

    Example - if we put a Final due date of 12/4/2015 then the intial due column field should fill the intial due date of 12/1/2015

    If Final Due was entered as 12/8/2015 then Initial Due would fill with 12/4/2015 - (this date because Sunday wouldnt count as a business day)

     

    I can do it in excel like this:

    Column A = Final Due Date

    Column B = number days between (example -3)

    Column C = Formula to enter the date not inclunding Sunday - "WORKDAY.INTL(A2,B2,11,A$123:A$134)"

     

    In this example in excel it works right

           A                           B                 C

    12/8/2015                    -3               12/4/2015 (autocalc from formula above)

     

    Hope I'm explainthing this correctly

     

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Jason,

     

    Go to the Gantt chart view and select the Project Settings (gear in the upper left of the Gantt).

    On Dependency Settings, select Dependencies Enabled.

    Change Working Days to include Saturday.

     

    This formula

     

    =NETWORKDAYS([Start]23, [Finish]23)

     

    will determine work days including Saturday (after the sheet is saved).

     

    for Start = 11/1/15 and Finish = 11/9/15, this results in 7 days.

     

    WARNING:

    Changing the dependencies and work days will impact all of your current dates.

    It will also add two new columns for Predecessors and Duration if you don't already have them.

    If dependencies are already on, then just change the Working Days.

    If not, I always save the dates in two new (temporary) columns until I have everything set up so I don't lose something.

     

    Hope this helps.

     

    Craig

     

     

     

     

     

     

     

     

     

     

     

    · Share on FacebookShare on Twitter
  • Jason S
    Jason S ✭
    edited 12/03/15

    Thanks Craig!  That formula works for counting the business days between 2 cells.

     

    Using your info about the Gnatt setting and using

    =WORKDAY([Closing Date]18, -3)

    I was able to get the following to work

    Entering Closing Date of 12/7/2015

    makes my Due Date alculate as 12/3/2015

    which is perfect, its counting Saturday as a business day now.

     

    Thanks for all your help with this!

     

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories