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,

As per your description, according to this article TODAY function  we cannot directly specify today in calculated column to set the values. You will have to create columns named Today and then use it in your calculated column.

You can use TODAY(),as a function that will be returned as DateTime.

Also , here I find alternately trick in this article you may refer and see if that meet your requirement: using \[Today\] in SharePoint Calculated Columns

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 3

Hi mdvant,

Sorry to hear you're experiencing this issue and I apologize for the inconvenience

As per your description, your IF statements are correct, and I should be work. However, I test from my side and it work e.g. see image below for your reference:

 

To narrow down your issue please try to use =IF([ExpiryDate]-[Today]>90,"Yes","No”) and please check you are using English language keyboard to enter symbol "

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 4

Hi Waqas

I tried all possible syntax combinations, but to no avail:

Title	[Title]	Separator (,)	Separator (;)	"X"	'X'
X		X		X
X		X			X
X			X	X
X			X		X
	X	X		X
	X	X			X
	X		X	X
	X		X		X

Even if the error message says it's a syntax error, could there be another issue? Like I said, simple functions like add and subtract works with the Today table, but as soon as I want to use an IF statement, nothing seems to work.

Answer 5

Thank you, I tried your suggestion of adding an extra column of Today, which returns today's date. I also added a workflow that would update the date every 12 hours as suggested by Nate Chamberlain - Creating a "Today" column in SharePoint that always gives today's date.

This works with simple formulas, like =[ExpiryDate]-[Today], which returns the difference in the number of days (i.e. The data type returned from this formula is: Number).

However, when I want to use IF statements, I receive a syntax error message. I tried different formatting:

=IF([ExpiryDate]-[Today]>90;"Yes";IF([Today]-[ExpiryDate]<=90;"Soon";IF([ExpiryDate]-[Today]<0;"No";IF(ISBLANK[ExpiryDate];"Not applicable"))))

Even a simple IF statement (like the one below) gives an error message:

=IF([ExpiryDate]-[Today]>90;"Yes";"No")

I also tried substituting the ';' with ','. The data type returned from this formula is: Single line of text.