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
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.
Comments
-
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
-
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
-
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
-
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
-
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives