0

I am working on a custom list inside a SharePoint online site collection. and i have a custom column named "Last Update" of type DateTime, and i want to create a calculated column to show the differences between today and the "Last Update" site column.

So i created a new site column of type Calculated column, with the following formula:-

enter image description here

and it worked well, but when i tried to add this column inside our list, i got this error:-

enter image description here

any advice?

1

Use below formula to calculate the difference between Today and Last Update Date column.

=IF(ISBLANK([Last Update]),"",(TEXT(NOW(),"mm/dd/yyyy")-[Last Update]))

and set The data type returned from this formula to : Number in order to get difference in Number of days between two dates.

2
  • your formula worked well if the [Last Update] contain a value, while if the [Last Update] is empty the formula will return 44,055.. can i fix this? to force the formula to return null if the [Last Update] is null?
    – John John
    Aug 12 '20 at 11:34
  • 1
    @SharePointTestDev , updated the formula to check if Last update is blank, then it will return blank. Kindly check. Aug 12 '20 at 13:00
1

We simply cannot use [Today] or [Me] in calculated columns. Use formulas like

=DATEVALUE(TEXT(NOW(),"mm/dd/yyyy")) - DATEVALUE(TEXT([Created],"mm/dd/yyyy"))

or =TEXT(NOW(),"mm/dd/yyyy")-[Created] will be workarounds you can use. And remember if you use DateValue function, you need to set the The data type returned from this formula is: to be date.

1
  • your formula worked well if the [Last Update] contain a value, while if the [Last Update] is empty the formula will return 44,055.. can i fix this? to force the formula to return null if the [Last Update] is null?
    – John John
    Aug 12 '20 at 11:33

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.