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.

    Record the remaining days on a project

    ✭
    in Formulas and Functions

    Hello,

    I have a start and end date for planned work. I'd like to see to show in a column the amount of days remaining on the project.

    The issue I run into with most formulas is if the start and end are in the future, the counts are all off, and I'd like it to not record anything if the end is in the past.

    I was using this formula to get the days remaining, but it doesn't take into account all the specifics I need.

    =IFERROR(NETWORKDAYS([Planned Work End]1, TODAY()) * -1, "")

    I'd like it to only show for ones where the Planned Work End is in the future, and show the amount of days between start and end, for projects where the start is in the future.

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭
      Answer ✓

      Try something like this:
      =IF([End Date]@row < TODAY(), "In the past", IF([Start Date]@row > TODAY(), NETWORKDAYS([Start Date]@row, [End Date]@row), NETWORKDAYS(TODAY(), [End Date]@row)))

      This will return:
      - "In the past" for anything that has an End Date in the past (can change to 0, or blank)
      - The number of working days between the start/end for anything starting in the future
      - The number of days remaining for anything that has already started and the end date is in the future.

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭
      Answer ✓

      Try something like this:
      =IF([End Date]@row < TODAY(), "In the past", IF([Start Date]@row > TODAY(), NETWORKDAYS([Start Date]@row, [End Date]@row), NETWORKDAYS(TODAY(), [End Date]@row)))

      This will return:
      - "In the past" for anything that has an End Date in the past (can change to 0, or blank)
      - The number of working days between the start/end for anything starting in the future
      - The number of days remaining for anything that has already started and the end date is in the future.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      Hi @Ian44

      have a try with this one.

      =IF(OR([% Complete]@row = 100, [End Date]@row < TODAY()), "", IF(ISERROR(NETWORKDAYS(TODAY(), [End Date}@row)), "", NETWORKDAYS(TODAY(), [End Date]@row)))

      It's working for me on a test sheet, but I haven't tried on a 'live' sheet.

      Paul Reeves. MBA, LSSBB

      · Share on FacebookShare on Twitter
    • ✭

      @LJ

      That worked like magic! Thank you so very much!

      · 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