0

I have two date fields date1, date2 and stores in column name result . I need the difference of the two dates to be calculated and store no of days in the result.

Problem here is with two cases:

  1. case 1: if date1 is lesser than date2 then Null error.
  2. case 2: if date2 is lesser than date1 then Null error.

Condition I placed is:

IF(OR(ISBLANK([Date1]),ISBLANK([Date2])),"",IF(ISERROR(DATEDIF([Date1][Date2])),"DATEDIF([Date2],[Date1],"D")",IF(ISERROR(DATEDIF([Date2][Date1])),"DATEDIF([Date1],[Date2],"D")")))

I am pretty confused in figuring out the trick. Help me in getting this.

3
  • In below comments you mentioned, you want to calculate based on Today's date. Do you want to calculate this everyday when (Today) changes? or only when saving the item? Nov 4, 2019 at 11:38
  • only while saving. Nov 4, 2019 at 12:08
  • If your Date1 > Today, then what do you want to show? Nov 4, 2019 at 12:18

2 Answers 2

0

There are some syntax errors in your formula (extra " and commas are missing at some places).

Try using below formula:

IF(OR(ISBLANK([Date1]),ISBLANK([Date2])),"",IF(ISERROR(DATEDIF([Date1],[Date2],"D")),DATEDIF([Date2],[Date1],"D"),IF(ISERROR(DATEDIF([Date2],[Date1],"D")),DATEDIF([Date1],[Date2],"D"),"")))
4
  • Still same error Ganesh. Nov 4, 2019 at 10:49
  • Actually the date1 column contains todays date and date2 contains <todays-date>(less than or greater than todays date). I need to get the difference of those tow columns. to get todays date(date1) I have followed sharepointpals.com/post/… this blog, and date2 column user enters some random date. result should come as the difference b/w days. if you have any other idea please suggest me. Nov 4, 2019 at 10:53
  • Try using this simple formula: IF(OR(ISBLANK([Date1]),ISBLANK([Date2])),"",DATEDIF([Date1],[Date2],"D")) Nov 4, 2019 at 10:59
  • Tried this, but DATEDIF([Date1],[Date2]) is giving a negative number also(if date1 is bigger than date2). thats why I have been trying for other approach. Nov 4, 2019 at 11:06
0

Instead of storing today's date in a column. You can use **TODAY()** in your calculated column which returns today's date. Try the formula below if it helps.

IF(ISBLANK([Date2]),"",IF(ISERROR(DATEDIF(Today(),[Date2],"D")), DATEDIF([Date2],Today(),"D"), DATEDIF(Today(),[Date2],"D")))
1
  • Today() is giving an error, trying to use a column which doesn't exist. Nov 4, 2019 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.