Skip to main content
Solved

Date countdown to follow up

  • August 17, 2020
  • 2 replies
  • 25 views
S

Hi I am converting a Google sheet to Airtable. In Google sheet I have a formula that:

  1. Counts down 7 days from Last touch to 0, this acts as a reminder.
  2. If a follow up date is set, it counts down the number of days from the reminder date
  3. If certain conditions are met (e.g. dead/stop) shows '-"

You can see it working here:

https://docs.google.com/spreadsheets/d/1sca1LgSu46ouOAigppAQYfQNwW48vOgu3c9wcux5HCE/edit?usp=sharing

The formula I am using is:

=if(and(F2>0,F2>E2),F2-today(),if(or(E2=0,C2=“11 - stop”,C2=“11 - dead”,C2=“09 - lead (Booked a call)”,C2=“10 - sale”,C2=“08 - email follow up sent”),"-",E2-today()+7))

Can you please help me to write this in Airtable?

Thank you

Best answer by Shaun_Conroy

This might work:

IF(AND(IS_AFTER({F2}, {E2}), IS_AFTER({F2}, TODAY())), DATETIME_DIFF({F2}, TODAY(), 'days'),IF(OR(LEN({C2}) = 0, {C2} = "11 - stop", {C2} = "11 - dead", {C2} = "09 - lead (Booked a call)", {C2} = "08 - email follow up sent", {C2} = "10 - sale"), "-", DATETIME_DIFF({E2}, TODAY(), 'days') + 7))

Here’s what the formula popup looks like with the above formula:

The resulting table:

I kept the field names using C2, E2, F2 etc. to make it easier to compare the formulas.

Once you’re satisfied the formula is working properly I would rename the fields to have better names (which will also automatically rename the fields in the formula for you).

Something like this:


Brilliant, thank you that worked!

    2 replies

    G
    • gwynn_kruger
    • Participating Frequently
    • 32 replies
    • August 17, 2020

    This might work:

    IF(AND(IS_AFTER({F2}, {E2}), IS_AFTER({F2}, TODAY())), DATETIME_DIFF({F2}, TODAY(), 'days'),IF(OR(LEN({C2}) = 0, {C2} = "11 - stop", {C2} = "11 - dead", {C2} = "09 - lead (Booked a call)", {C2} = "08 - email follow up sent", {C2} = "10 - sale"), "-", DATETIME_DIFF({E2}, TODAY(), 'days') + 7))

    Here’s what the formula popup looks like with the above formula:

    The resulting table:

    I kept the field names using C2, E2, F2 etc. to make it easier to compare the formulas.

    Once you’re satisfied the formula is working properly I would rename the fields to have better names (which will also automatically rename the fields in the formula for you).

    Something like this:

    S
    • Shaun_Conroy
    • Author
    • New Participant
    • 1 reply
    • Answer
    • August 18, 2020

    This might work:

    IF(AND(IS_AFTER({F2}, {E2}), IS_AFTER({F2}, TODAY())), DATETIME_DIFF({F2}, TODAY(), 'days'),IF(OR(LEN({C2}) = 0, {C2} = "11 - stop", {C2} = "11 - dead", {C2} = "09 - lead (Booked a call)", {C2} = "08 - email follow up sent", {C2} = "10 - sale"), "-", DATETIME_DIFF({E2}, TODAY(), 'days') + 7))

    Here’s what the formula popup looks like with the above formula:

    The resulting table:

    I kept the field names using C2, E2, F2 etc. to make it easier to compare the formulas.

    Once you’re satisfied the formula is working properly I would rename the fields to have better names (which will also automatically rename the fields in the formula for you).

    Something like this:


    Brilliant, thank you that worked!


    Terms & ConditionsAccessibility statement