Help

Running Countdown based on single select stage

Topic Labels: Formulas
Solved
Jump to Solution
1418 3
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Gutierre1
5 - Automation Enthusiast
5 - Automation Enthusiast

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
Jason
Airtable Employee
Airtable Employee

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
Jason
Airtable Employee
Airtable Employee

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!

David_Gutierre1
5 - Automation Enthusiast
5 - Automation Enthusiast

Well that definitely worked. Thanks a ton!!

Happy to help out David!