Re: Put a value when event occur between two dates

Florence_Dorsil · ‎Sep 17, 2020

Hi There,

I’m having a hard time figuring out how to put a value when a subscription occurs between to date :

I’ve tried this :arrow_down:

IF(
{Date} < ‘30/09/2020’,
“TPP Test phase”,
IF(
{Date} < ‘31/12/2020’,
“TPP OND”,
IF({Date} < ‘31/03/2021’,
“TPP JFM”,
IF({Date} < ‘30/06/2021’,
“TPP AMJ”
)
)
)
)

But it won’t work at all. Someone could help please ?

ScottWorld · ‎Sep 17, 2020

Welcome to the community, @Florence_Dorsile!

The list of functions that you can use in Airtable is on this page:

In your case, you would use the IS_BEFORE function (and optionally, the DATETIME_PARSE function):

IF(
    IS_BEFORE({Date}, DATETIME_PARSE('30/09/2020','DD/MM/YYYY')),
    "TPP Test phase",
IF(    
    IS_BEFORE({Date}, DATETIME_PARSE('31/12/2020','DD/MM/YYYY')),
    "TPP OND",
IF(    
    IS_BEFORE({Date}, DATETIME_PARSE('31/03/2021','DD/MM/YYYY')),
    "TPP JFM",
IF(    
	IS_BEFORE({Date}, DATETIME_PARSE('30/06/2021','DD/MM/YYYY')),
	"TPP AMJ" 
))))

If you’re already in a country where the dates are formatted in DD/MM/YYYY, you wouldn’t need to use the DATETIME_PARSE part of the function, but to avoid any ambiguity whatsoever (regardless of country), you would want to use the DATETIME_PARSE function.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

See Solution in Thread

ScottWorld · ‎Sep 17, 2020

Welcome to the community, @Florence_Dorsile!

The list of functions that you can use in Airtable is on this page:

In your case, you would use the IS_BEFORE function (and optionally, the DATETIME_PARSE function):

IF(
    IS_BEFORE({Date}, DATETIME_PARSE('30/09/2020','DD/MM/YYYY')),
    "TPP Test phase",
IF(    
    IS_BEFORE({Date}, DATETIME_PARSE('31/12/2020','DD/MM/YYYY')),
    "TPP OND",
IF(    
    IS_BEFORE({Date}, DATETIME_PARSE('31/03/2021','DD/MM/YYYY')),
    "TPP JFM",
IF(    
	IS_BEFORE({Date}, DATETIME_PARSE('30/06/2021','DD/MM/YYYY')),
	"TPP AMJ" 
))))

If you’re already in a country where the dates are formatted in DD/MM/YYYY, you wouldn’t need to use the DATETIME_PARSE part of the function, but to avoid any ambiguity whatsoever (regardless of country), you would want to use the DATETIME_PARSE function.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Florence_Dorsil · ‎Sep 17, 2020

ScottWorld:

IF(
    IS_BEFORE({Date}, DATETIME_PARSE('30/09/2020','DD/MM/YYYY')),
    "TPP Test phase",
IF(    
    IS_BEFORE({Date}, DATETIME_PARSE('31/12/2020','DD/MM/YYYY')),
    "TPP OND",
IF(    
    IS_BEFORE({Date}, DATETIME_PARSE('31/03/2021','DD/MM/YYYY')),
    "TPP JFM",
IF(    
	IS_BEFORE({Date}, DATETIME_PARSE('30/06/2021','DD/MM/YYYY')),
	"TPP AMJ" 
))))

Thanks a lot !!! :hugs: