CONDITIONAL FORMATTING FOR DATE TYPE CELL

C. Srinidhi Ragunathan

Greetings

I need to apply conditional formatting to a date type cell. I need to write a formula such that, when a the target number for a certain task has been reached, the deadlines for the next target has to be set. 

For Example, if the target for task A has reached today, then the start date for the next task should be set two days later. 

I tried doing this, however the date column (cell) is not taking in equations. 

Can anyone suggest some roundabout ?

 

Thank you

sean59916

Srini - 

I'm not sure if you are looking for help with conditional formatting or help with a formula?

If you are looking for a formula that sets the date for subsequent task, you could use something like this (see image):

=IF(Status1 > 0.8, Date1 + 2, "")

Essentially, reading the status column and once it get's to a certain level it adds 2 days to the date, else null.

 

I hope this helps, if not let me know.

Sean

Andrée Starå

Hi,

To add to Sean's answer.

If you have a TODAY function in the formula, it will always update to the current date (Today's Date) so if you want it to stay fixed you have to use a third-party solution like Zapier or similar or the Smartsheet API for that to work.

Please submit an Enhancement Request when you have a moment.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

C. Srinidhi Ragunathan

Hi Sean, 

I think the way I framed the question was a bit confusing. But you did clear my doubt!

However I realized, I have to create a new date column as I am not able to modify the cells under my Start and End date column (Check screenshots).

Thank you anyway

Paul Newcome

With dependencies enabled, you can set a predecessor and then add lag/lead time.

I have found that (for me personally) dependencies are more of a hindrance than a help.

I typically use two sets of date type columns. One for projected and one for actual. Doing it this way allows me to use formulas within the date columns regardless of whether it is the projected or actual set.

This also gives me a baseline to work off of to determine whether or not the project is on track.

C. Srinidhi Ragunathan

Hi,

Thank you for the suggestion. I was thinking of doing something on similar lines.

However, it is not possible to project both the timelines while viewing the tasks in a Gantt version right ?

Thank you

Andrée Starå

Unfortunately, no it's not! You would have to have separate rows to see them together or use a third-party solution.

Hope that helps!

Best,

Andrée

Paul Newcome

Andree is correct. However... Depending on your setup there is a variation that may work. I use it for all of my projects.

 

I'll use 3 more helper columns. The first column is up to you whether it be date or text/number. I use text/number and then put the numbers 0 - ## on down the line. My next two columns are Projected Percent Complete and Actual Percent Complete.

 

Projected percent complete is easy. Especially if you are using a date type column. Simply figure out how many tasks should be completed by the date on the row and divide it by the total number of tasks.

 

Actual percent complete is the same thing except basing it off of the actual complete dates. I usually start that formula out with an IF statement saying that if today is greater than or equal to the date on the row, run my calculation. This leaves future dates as blank cells.

 

You can then use these columns in a chart widget on a dashboard to show your projected vs actual. In the screenshot below you will see what I mean and why I leave future dates blank in the Actual Column...

 

P.S.

The project I pulled this graph from was an absolute disaster. Don't judge me for this one. Hahaha