2

I am running a SharePoint formula that calculates the length of time (in yrs, months, and days) before a project expires.

In the "Will Expire" column the following formula is running:

=DATEDIF(CurrentDate,[End Date],"Y")&" Years "&DATEDIF(CurrentDate,[End Date],"YM")&" Months "&DATEDIF(CurrentDate,[End Date],"MD")&" Days"

The "CurrentDate" column is updated daily as part of a Power Automate job. The "End Date" column is manually added as part of the project term.

For expired projects, a result is a negative number which is an error with DATEIF that shows up as #NUM!.

I do not want negative numbers to show and used DATEDIF to get an error in hopes of changing the error to something more user friendly.

However when adding an ISERROR statement - I cannot get the syntax of the combined statements to work.

Any help or ideas to use a better way appreciated.

Improve this question

1 Answer 1

Reset to default
1

To avoid #NUM!, you should use ISERROR as below

=IF(ISERROR(DATEDIF(CurrentDate,[End Date],"Y")),"show error message",DATEDIF(CurrentDate,[End Date],"Y"))

To match this with your formula, the final formula with ISERROR should like

=CONCATENATE(IF(ISERROR(DATEDIF(CurrentDate,[End Date],"Y")),"0",DATEDIF(CurrentDate,[End Date],"Y"))," Years ",IF(ISERROR(DATEDIF(CurrentDate,[End Date],"YM")),"0",DATEDIF(CurrentDate,[End Date],"YM"))," Months",IF(ISERROR(DATEDIF(CurrentDate,[End Date],"MD")),"0",DATEDIF(CurrentDate,[End Date],"MD"))," Days")

Output

On error for all conditions, it should show

0 Years 0 Months 0 Days

ISERROR returns true if there is an error. so that in the If clause. if there is an error, it will show your preferred statement. otherwise, it would calculate the DATEDIF. Read also Supported and Unsupported Columns in SharePoint Calculated Column Formula

Improve this answer
7
  • Thanks for the answer. SharePoint gives a "The formula contains a syntax error or is not supported" error on the final formula. I will keep working on it.
    – DK524
    Commented Dec 31, 2020 at 21:31
  • I suggest to first try this line =IF(ISERROR(DATEDIF(CurrentDate,[End Date],"Y")),"show error message",DATEDIF(CurrentDate,[End Date],"Y")) and let me know if it raises any error
    – Mohamed El-Qassas MVP ♦
    Commented Dec 31, 2020 at 21:33
  • The problem seems to be with the text of "Years, Months and Days". Looks like I'm trying to do to much in this column. Think I will abandon trying things this way. Appreciate the help.
    – DK524
    Commented Dec 31, 2020 at 21:54
  • I would suggest using CONCATENATE, I will update the answer, and please check and let me know
    – Mohamed El-Qassas MVP ♦
    Commented Dec 31, 2020 at 21:57
  • @DK524 I have updated the answer, please check it!
    – Mohamed El-Qassas MVP ♦
    Commented Dec 31, 2020 at 22:06

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.