How do l stop a countdown??

Silvia Rangel

Hello Community;

is there a way to stop a countdown? I am using a countdown to show how many days are left/or past due to a specific date and it works as intended, however, I need this countdown to stop once the actual completion date is entered the countdown stops so that it shows either how many days they were past due or how many days they completed the job ahead of time. Is this possible??

Thanks!

Kyle Chipman

Hi Silvia. You'll just want to replace the "" portion of the formula with the NETDAYS formula. Didn't realize you wanted these in the same column.

Did the methodology used make sense? In the example above, the -17 is showing that we're 17 calendar days past the Must Finish By date (in this case, April 4th). The -35 means that, for an April 4th MFB, your Completion Date is 5/8. See the screenshot below, in addition to the revised formula.

Also, be sure to dig in to and understand @row, it's a great habit to bake in to your formulas

=IF(ISDATE([Completion Date]@row), NETDAYS([Completion Date]@row, [Must Finish By Date]@row), [Must Finish By Date]@row - TODAY())

Kyle Chipman

Totally. You'll want to use a combination of an =IF statement to stop the countdown, and =NETDAYS today calculate the +/- difference between your Must Finish By and Completion dates.

First, I use @row throughout; it's a really helpful habit to get into when building formulas.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

I'll list the screenshot below, then paste the formulas I used and talk through why I set them up the way I did (the columns marked in blue are using formulas):

COUNTDOWN TO MFB

=IF(ISDATE([Completion Date]@row), "", [Must Finish By Date]@row - TODAY())

If there's a date listed in Completion Date, the Countdown column will return nothing, designated by the "". An interesting side note - if you need to track business days as opposed to calendar days, you can play around with NETWORKDAYS(TODAY(), [Must Finish By Date]@row) on the ELSE half of the IF formula. However, it isn't quite as one-to-one as the calendar day version. Try it out for yourself.

COMPLETION DATE DIFFERENCE

=IF(ISERROR(NETDAYS([Completion Date]@row, [Must Finish By Date]@row)), "", NETDAYS([Completion Date]@row, [Must Finish By Date]@row))

The crux of this formula is NETDAYS([Completion Date]@row, [Must Finish By Date]@row). This will give you the ahead of / behind schedule counter you're looking for. The reason there's an IF(ISERROR(... portion to it is to prevent the sheet from returning a bunch of #INVALID DATA for rows without a Completion Date entered. Again, try it out for yourself to get a feel.

Let me know if this helps!

Silvia Rangel

Hello @Kyle Chipman thanks for the input! I tried them both and the COUNTDOWN TO MFB is giving me a blank cell once the completion date is entered, ideally we need it to show the actual quantity of days that passed until the completion date was entered:

The COMPLETION DATE DIFFERENCE option is showing a different number of days, as of today we are at -17 days in the MFB countdown, but the formula is showing -35 days :

l am thinking that maybe a need another column to count the actual days between the contractor receiving the job, the MFB, and the Actual Completion Date?

Thanks!

Kyle Chipman

Hi Silvia. You'll just want to replace the "" portion of the formula with the NETDAYS formula. Didn't realize you wanted these in the same column.

Did the methodology used make sense? In the example above, the -17 is showing that we're 17 calendar days past the Must Finish By date (in this case, April 4th). The -35 means that, for an April 4th MFB, your Completion Date is 5/8. See the screenshot below, in addition to the revised formula.

Also, be sure to dig in to and understand @row, it's a great habit to bake in to your formulas

=IF(ISDATE([Completion Date]@row), NETDAYS([Completion Date]@row, [Must Finish By Date]@row), [Must Finish By Date]@row - TODAY())

Silvia Rangel

@Kyle Chipman Yes, it makes sense, the actual completion date l have for testing this row is 5/8 ☺Another silly question; once the completion date is entered and using this formula, the days in this column (Countdown to MFB) will remain the same with any passing day, correct?? As currently this number changes everyday.... Thanks!