0

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

2

See: How to use Today and Me in Calculated column

0

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

0

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.

  • I get error as The formula contains a syntax error or is not supported. – Brishal Apr 7 '15 at 9:05
  • have you deleted the column "Today" as I explained in step 3 ? – Zakir HC Apr 7 '15 at 9:15
  • But Calculated Formulas are only updated when the Item is updated, so what does this [CurrentDate] 'calculate' tomorrow?? – Danny '365CSI' Engelman Apr 11 '15 at 9:21

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.