Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 10, 2021 07:26 AM
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
Jun 10, 2021 07:59 AM
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')
)
Jun 10, 2021 08:20 AM
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
Jun 10, 2021 09:24 AM
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'
)
)
Jun 11, 2021 12:52 AM
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 ?
Jun 11, 2021 02:12 AM
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
Jun 11, 2021 08:33 AM
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.