calculated formula for date in sharepoint

Question

I have a date field "Start date" and want to create calculated field based on below condition

1. If start date is less than current date enter value "Progress"
2. If start date is greater than current date enter value "Resolved"

Please help me with formula to be used. Thanks

Answer 1

See: How to use Today and Me in Calculated column

Answer 2

I would suggest you to create a SharePoint Designer workflow for this requirement, as a Calculated column doesn-t support Today function.

Answer 3

SharePoint has a tricky workaround that allows "Today" function. It is not a perfect workaround but it does the job.

1. First, create a new column in your list with the Column Name "Today" (It doesn't matter what type of column or data it is, this is just a place holder. I created a single line of text column.)

2. Create a new a calculated column and name it as "CurrentDate" (or anything you may prefer). Remember to choose "Date and Time" as the return data type for this calculated column. Your formula must be

   =[Today]
   

3. The above step will update an old date in your "CurrentDate" column but do not worry about it. To get the actual system current date simply delete the "Today" column that you created in first step. SharePoint automatically takes the reference of today's date when you had put the formula

   =[Today]
   

   Note: If you want to edit the formula containing "Today" SharePoint will not let you, and will give you the same error about not using Today in a function. To get passed this problem you have to temporarily create another Today column.

4. Create a new calculated column, example "Status" and the return type must be single line of text. Now put the formula as

   =IF(StartDate>CurrentDate,"Resolved","Progress")
   

   Here the StartDate is your Start date column that you had already created.

   Update a value in your Start date column and see the result in "Status" column.