Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Running Countdown based on single select stage

Topic Labels: Formulas
Solved
Jump to Solution
779 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello all!

I want to have countdown (showing how many days left) based on the stage (a single select field) of a record. Each stage should take a certain amount of time to complete, so the goal is to see how many days left I have to complete a stage. Then once I change it to the next stage, that countdown is reset to the parameters of the next stage.

I have parameters are mostly set by formula-based date fields that live in the table. So it would look something like this:
If {Stage}=Stage 1, calculate difference between {date 1} & 14 days from {date 1}
If {Stage}=Stage 2, calculate difference between {date 2} & 14 days from {date 2}
If {Stage}=Stage 3, calculate difference between {date 3} & 28 days from {date 3}
If {Stage}=Stage Complete, “Complete”

I tried doing a DATETIME_DIFF inside of the If function, but have been struggling with errors. Seems pretty straight-forward, but I can’t figure it out…

Any help would be great! Thanks in advance.

1 Solution

Accepted Solutions

Hi @David_Gutierrez,

If I’m understanding your question correctly, you’d like to:

  • Add a certain number of days to {Date} based on {Stage}
  • Find the difference between the current time and whatever date was added from the step above

If that’s correct, here’s a formula you can use:

SWITCH(
   {Stage},
      "Stage 1", 
         DATETIME_DIFF(DATEADD({Date},14,'days'),NOW(),'days'),
      "Stage 2", 
         DATETIME_DIFF(DATEADD({Date},14,'days'),NOW(),'days'),
      "Stage 3", 
         DATETIME_DIFF(DATEADD({Date},28,'days'),NOW(),'days'),
      "Complete", "Complete"
)

This uses the SWITCH() function rather than IF statements for a slightly more straightforward approach. You’ll see in the GIF below how the countdown adjusts based on the entered date and the current time (the NOW() function in the formula).

Screen Recording 2020-07-02 at 12.17 PM

Let me know if that helps!

See Solution in Thread

3 Replies 3

Hi @David_Gutierrez,

If I’m understanding your question correctly, you’d like to:

  • Add a certain number of days to {Date} based on {Stage}
  • Find the difference between the current time and whatever date was added from the step above

If that’s correct, here’s a formula you can use:

SWITCH(
   {Stage},
      "Stage 1", 
         DATETIME_DIFF(DATEADD({Date},14,'days'),NOW(),'days'),
      "Stage 2", 
         DATETIME_DIFF(DATEADD({Date},14,'days'),NOW(),'days'),
      "Stage 3", 
         DATETIME_DIFF(DATEADD({Date},28,'days'),NOW(),'days'),
      "Complete", "Complete"
)

This uses the SWITCH() function rather than IF statements for a slightly more straightforward approach. You’ll see in the GIF below how the countdown adjusts based on the entered date and the current time (the NOW() function in the formula).

Screen Recording 2020-07-02 at 12.17 PM

Let me know if that helps!

Well that definitely worked. Thanks a ton!!

Happy to help out David!