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.

  • 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? – harshal gite Nov 4 '19 at 11:38
  • only while saving. – Gowtham Vemuri Nov 4 '19 at 12:08
  • If your Date1 > Today, then what do you want to show? – harshal gite Nov 4 '19 at 12:18
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"),"")))
| improve this answer | |
  • Still same error Ganesh. – Gowtham Vemuri Nov 4 '19 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. – Gowtham Vemuri Nov 4 '19 at 10:53
  • Try using this simple formula: IF(OR(ISBLANK([Date1]),ISBLANK([Date2])),"",DATEDIF([Date1],[Date2],"D")) – Ganesh Sanap Nov 4 '19 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. – Gowtham Vemuri Nov 4 '19 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")))
| improve this answer | |
  • Today() is giving an error, trying to use a column which doesn't exist. – Gowtham Vemuri Nov 4 '19 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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