0

Mind to help why this formula return error.

do note

StartDate and EndDate column is Date and time type while this calculated column will be single line of text

IF([StartDate]> [TODAY], "Upcoming", IF([EndDate]< [TODAY], "Expired", "Due"))

Updated(correct column name)

IF([Start Date]> [TODAY], "Upcoming", IF([End Date]< [TODAY], "Expired", "Due"))

now show Calculated columns cannot contain volatile functions like Today and Me.

Thank you

Improve this question
4
  • What is the data type of the Calculated Column you are attempting to apply this formula to?
    – A.k.A. Fritz
    Jan 6, 2022 at 3:52
  • Also, SharePoint Calculated Fields use the same notation as Excel formulas. They all begin with ‘=‘ (no quotes) in front of the ’if’ statement.
    – A.k.A. Fritz
    Jan 6, 2022 at 3:54
  • data type is line of text I guess..
    – NewbieInSharepoint
    Jan 6, 2022 at 4:13
  • update.. i try to change TODAY to NOW() and it works. =IF([Start Date]> NOW(), "Upcoming", IF([End Date]< NOW(), "Expired", "Due")).. Is this column will always auto update if come in next day ?
    – NewbieInSharepoint
    Jan 6, 2022 at 4:19

2 Answers 2

Reset to default
0

Well, you should go into the settings of the list or library and scroll down and find the calculated column and ensure that the value is either ‘single line of text’ or a ‘date’. A single line of text is just fine.
Here is the formula that you need to copy and paste into the calculated column’s formula field. (Note the placement of the last and 2nd to last parenthesis.)

=IF([Start Date] > [Today], "Upcoming", IF([End Date] < [Today], "Expired"), "Due")

Basic logic explained:

If start date is less than Today, mark as “Upcoming”, otherwise, if end date is less than Today, mark as “Expired”, if neither condition is true, mark item as “Due”.

Your previous placement of the second to last parenthesis after your formula’s “Due” clause would have equated to:

If start date is less than Today, mark as “Upcoming”, otherwise, if end date is less than Today, mark as “Expired”, but if end date is NOT less than Today, mark the item as “Due”.

There are a couple better ways to write your formula, but you should be fine with my updated one laid out above.

Improve this answer
0

In addition, you could also check the difference between Today() and Now().

The difference is Now() uses date and time, so you’ll get down-to-the-minute values (i.e. hours since reported). Today is just date best used for data with no times involved (i.e. days since hire date). The following is a side-by-side comparison using the same “Created” date subtracted from Today() and Now(). You’ll notice the value using Now() is a little higher because it includes the hours already passed today whereas Today() hasn’t changed since midnight.

See this blog: Using today’s date and/or current time in calculated columns and list view filters

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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