Smartsheet Function that Calculate Number of Days Between Today's Date and the Task End Date

Des
Des
edited 03/27/22 in Formulas and Functions

Hi,

I need help creating a formula to help me a couple of things.

1) First, I want to calculate the number of days from Today to the End Date of a task.

2) Then based on the number returned, I want to setup Conditional Formatting to highlight the Status column Red (0-2 days), Yellow (3-4 days) or Green (5+ days).

Is this the NETWORKDAY Function and if so, can you share how to set this up or what the formula is?

Thanks for your help!

Des

Tags:

Answers

  • J Tech
    J Tech ✭✭✭✭✭

    Hi Des,

    In your days remaining you could enter [End Date]@row-Today()

    Based on the above you can simply setup conditional formatting.

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • Des
    Des
    edited 03/28/22

    Thanks, JTech. I was able to configure the following formula which worked:

    =NETWORKDAYS(TODAY(), [End Date]@row)

    I then used that column to add Red, Yellow and Green symbols with this formula:

    =IF([Working Days]@row < "4", "Red", IF([Working Days]@row < "6", "Yellow", IF([Working Days]@row > "5", "Green")))


    But how do I stop the RYG formula from populating blank cells, like cells in the parent row and a few rows of text above the actual project schedule we we've entered some additional text?


    Des

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!