Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭
    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:
    · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭

      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!
      · Share on FacebookShare on Twitter
    • ✭✭
      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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions