Conditional Formating between 2 dates

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.

Find more posts tagged with

Finance

Formulas

IT & Operations

Conditional Formatting

Project Management

Comments

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

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

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

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

Jason S

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!