DATETIME_DIFF Formula

[Mathilde_Cotria]

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.

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

[Kamille_Parks]

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]

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

[Kamille_Parks]

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'
   )
)

[Mathilde_Cotria]

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

DATE ENTREE

FIN PE

DELAIS DE PREVENANCE

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

[Mathilde_Cotria]

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

[Kamille_Parks]

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.