Use TODAY() function in SharePoint list

Question

I recently moved from an Excel spreadsheet to a SharePoint list. I had a column in Excel where a status is returned based on the following formula:

=IFERROR(IF( [ExpiryDate] - TODAY() > 90; "Yes"; IF( TODAY() - [ExpiryDate] <=90; "Soon"; IF( [ExpiryDate] - TODAY() < 0; "No";  IFNA( [ExpiryDate]; "Not applicable")))); "Not applicable")

This formula evaluates the difference between the ExpiryDate (a date type column) and today's date (the TODAY() function in Excel), and based on the result, returns the status: "Yes" if more than 90 days, "Soon" if between 90 and 0 days, "No" if negative, or "Not applicable" if there was no data/entry in the ExpiryDate field.

However, converting to Sharepoint list, I gathered that the TODAY() function does not work in SharePoint. Is there an alternative solution to this one?

Answer 1

Wow, did some trouleshooting: I tested several IF statements and it seemed the problem is the IF statement only recognised calculations with integers. I therefore altered the formula to use the INT() function to convert the calculation to integers before executing the condition.

=INT(DATEDIF(Today,ExpiryDate,"D"))

Thank you for your assistance.

Answer 2

Hi mdvant,

We appreciate that you spent your precious time doing those tests and shared the test results with us. I do understand the inconvenience it has made and apologize for it. And we’re still glad to help you.

I think issue related with your Language settings.  Please try to check your Language settings of your SharePoint environment. please go to list/library>Setting>site information>View all site settings> Regional settings.

To narrow down this issue, could you provide the information below?

1.  Do all the lists and sites have the same problem? You can also try creating a new list to check if the same problem will occur.

2.Could you please confirm does same issue (IF statement syntax error) happen to another user in your organization.

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 3

Hi mdvant,

Thanks for your valuable feedback, glad to know that issue has been resolved.

Comments and feedback from our customers really motivate us to perform better and better and provided as much help as possible. We appreciate your kind words.

If there is any issue related with the Office applications in the future, please feel free to post back in this community.

We will be here for help.

Have a good day and stay safe 😊

Best Regards,

Waqas Muhammad