How do I use TODAY and where do I add the formulas in Microsoft Lists?

Question

I have 2 columns in Microsoft Lists: End Date (the date a contract ends) and Status (Showing if the contract is active or expired with color).

End Date	Status
01/09/2023	Expired
02/09/2023	Active

I would like the Status column to automatically show if a contract is "expired" or "active" based on the current date "todays", but I can't seem to find the correct formula or location to place the formula. And I would love it if it could change the color to correspond with the status either in the same cell or another. Do I need to make a whole column with todays date (over 400) just so Lists can see what day it is?

Thank you!

Answer 1

Dear respected GPalen,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, in SharePoint online list, we can use the TODAY function only as a default value, but we cannot use it in a calculated column.

The solutions are to be used in a calculated column and insert formula in calculated column.

Here are the steps:

1.Create another column (calculated column and use TODAY () formula) in the SharePoint online list called Today and set it as a calculated column with formula as Today. This column can be hidden in views (if you want to hide).

2. Use the column name [Today] which will actually contain the value of Today and [End Date] in a calculate column to show the Status.

Result:

Regarding to change the color you need to format the status column via click on column>Column settings>Format this column and set the Conditional Formatting.

Note: This method fills the whole cell

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 2

Thank you so much for your reply and assistance with this. Unfortunately, I'm still unable to obtain your results for the first part. Please see below:

Here are the results:

Results are showing every Status line as "Active".

The "End Date" column is a single line of text, does it need to be changed to something else in order for the calculations to be correct?

Thank you for all your help!

Answer 3

Yes, it worked! Thank you so much for all your help!

I know this is a separate issue, but would you know if there was a way to add another item (row) in between 2 rows? If I click on the little square, it just deleted Row 3 and copies Row 2 information.

turns it into this:

Thank you!

Answer 4

Hi respected GPalen,

Thanks for your valuable feedback and sharing updates with us and I really appreciate your effort and your precious time doing. 

And I appreciate your kind words.

As you mentioned, "The "End Date" column is a single line of text," the single line of text will not correct result in a calculated column. Thats why you get "Results are showing every Status line as "Active". You may use "Date and time" column for "End Date" column and get the result.

In your SharePoint list go to list settings option>click on your End date column to open and then click on "Date and Time" column (to change column type) then click on "Ok" to save change and I believe it will show you the correct result.

For your reference screenshot:

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Waqas Muhammad

Answer 5

Hi respected GPalen,

I'm writing a follow up this case, may I know have you checked my above reply? Feel free to post back if you need further assistance.

Sincerely, 

Waqas Muhammad