Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

DATETIME_DIFF Formula

Topic Labels: Formulas
1798 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Mathilde_Cotria
4 - Data Explorer
4 - Data Explorer

Hello :slightly_smiling_face:
New topic new question :
I want to do a DATEDIFF formula really basic).
I did : DATETIME_DIFF({FIN PE},{DATE ENTREE},‘days’)
But it’s really weird it work for some lines and don’t for the others.
AIRTABLE PREVENENCE

No links between the lines there are working with …
The two fields use in the formula are two dates with european format.

I don’t understand where i’m going wrong … please help

6 Replies 6

Without seeing the date fields in question, you are probably getting NaN because one or both the date fields are empty.

IF(
   AND({FIN PE},{DATE ENTREE}),
   DATETIME_DIFF({FIN PE},{DATE ENTREE}, 'days')
)
Mathilde_Cotria
4 - Data Explorer
4 - Data Explorer

The first date field is enter manually in a european format et the second date field uses in the DATETIMEDIFF formula is the result of these formula :

IF(AND({PERIODE ESSAI ACTIVE}=“OUI”,{PE A RENOUVELLER }=0),DATETIME_FORMAT(DATEADD({DATE ENTREE},{DUREE PE (MOIS)},‘month’),“DD/MM/YYYY”), IF(AND({PERIODE ESSAI ACTIVE}=“OUI”,{PE A RENOUVELLER }=1),DATETIME_FORMAT(DATEADD({DATE ENTREE},{DUREE PE (MOIS)}*2,‘month’),‘DD/MM/YYYY’)))

Maybe the probleme is coming from my formula who is totally working for the moment … :face_with_raised_eyebrow:

Both of my date field are full

pleaaaaase help

Try taking out the DATETIME_FORMAT()s and format the Formula’s output in the configuration settings just like you did for the Date field.

Try this simplified version of that formula:

IF(
   {PERIODE ESSAI ACTIVE}="OUI",
   DATEADD(
      {DATE ENTREE},
      {DUREE PE (MOIS)} * SWITCH({PE A RENOUVELLER }, 0, 1, 1, 2),
      'month'
   )
)

The formula is working :ok_hand: and i set the formula configuration but the DATETIMEDIFF formula continues to fail.

Delais de prévenance globale

DATE ENTREE

DATE ENTREE

FIN PE

FIN PE

DELAIS DE PREVENANCE

DELAIS DE PREVENANCE

I don’t know if all the screens could help to find my error ?

There is finally a problem with the simplified formula for “FIN PE”, the number of months included in “DUREE PE (MOIS)” is not added to “FIN PE” and does not double the number of months of “DUREE PE (MOIS)” to be added to “FIN PE” if “PE A RENOUVELER=1”.

I tried this : IF({PERIODE ESSAI ACTIVE}=“OUI”,DATEADD({DATE ENTREE},{DUREE PE (MOIS)},‘month’),IF(AND({PERIODE ESSAI ACTIVE}=“OUI”,{PE A RENOUVELLER }=1),DATEADD({DATE ENTREE},{DUREE PE (MOIS)}*2),‘month’))

But it’s not working properly

IF(
   {PERIODE ESSAI ACTIVE}="OUI",
   SWITCH(
       {PE A RENOUVELLER}, 
       0, DATEADD({DATE ENTREE}, {DUREE PE (MOIS)}, 'months'), 
       1, DATEADD({DATE ENTREE}, {DUREE PE (MOIS)} * 2, 'months')
   )
)

Make sure the value of the {DUREE PE (MOIS)} is a number.