How to create a calculated field that can be over ridden by the user if needed

Question

_{Thursday, August 20, 2015 11:49 PM}

I need a field to calculate the Annual Renewal Date of a contract (3 months prior to the End Date) and allow the user to override the Annual Renewal Date value if need be.

Brief:
Field A = Annual Renewal Date (calculated value)
Field A formula: =DATE(YEAR([End Date]),MONTH([End Date])-3,DAY([End Date]))

Field B = Annual Renewal Date (manual date entry)

Question:
So I need field 'A' to automatically calculate the renewal date 3 months prior to 'End Date', unless Field B has a value manually entered into it by the user; in which case Field A would be hidden from the list view.

Is this possible ? I've looked all over the internet for the answer and not been successful.

Additionally, as far as I can tell I can't create a default value field (which is what I wanted to do in the first place) because the Annual Renewal Date is calculated with a field name that can't be referenced (this being 'End Date').

Open to any suggestions.

All replies (7)

_{Tuesday, August 25, 2015 6:27 AM ✅Answered | 1 vote}

Hi Jasmine,

As croute1 suggested, we can create a custom workflow to achieve it. This workaround, the all items page will display only one field.

If “Filed B” is blank, “Field A” will automatically calculate the renewal date 3 months prior to 'End Date'.

If “Field B” is not blank, the value in “Field B” will be set to “Field A”.

If “Field A” is a calculated column, it cannot be edited, so we may create it with “Date and Time” type.

You could reference the steps below I have tested:

1. Create “End Date”, “Field A” and “Field B” with “Date and Time”. Then go ion your content type page and set the status of “Field A”  to “Hidden”. It makes sure “Field A” will not display when we add or edit item.

2. When creating a workflow in SharePoint Designer, we want to use a condition that check if a date field is blank. There is no option in the drop-down for is empty like there is with text fields. So we can create a list column of “Date and Time” type called “Empty Date” and leave it blank.

Set it's status to “Hidden”

3. Click “Create a workflow in SharePoint Designer ”

4.Here is my workflow for you reference:

6. Publish this workflow.

7. Go back to your site, click “Modify View” on the ribbon. Uncheck the columns you do not need and then click ok.

Best regards,

Linda Zhang

_{Friday, August 21, 2015 8:34 AM | 1 vote}

Hi,

From your description, it seems that the Field A is not created as the calculated type column, so the formula cannot reference the End Date column.  I recommend to create the column in Calculated type and then the formula can refer the End Date column.

To achieve your requirement, we can enter the Field A formula as below. In this way, the value of Field A will be blank if Field B is not empty:

IF(ISBLANK([Field B]),DATE(YEAR([End Date]),MONTH([End Date])-3,DAY([End Date])),"")

Here is my screenshot for your reference:

Best regards,

Linda Zhang

_{Friday, August 21, 2015 6:14 PM | 1 vote}

Hi Web Part- If I understand you, you don't necessarily need to override the calculated field, you just want to show one field or the other based on whether there's a value in B or not, correct? Why don't you have both fields and use a custom workflow to grab the value from one field or the other, based on the input, and copy that value to another field, which will always show?

cameron rautmann

_{Monday, August 24, 2015 11:36 PM}

Thanks for the reply, you are correct: I'm new to workflows and am unsure how I should approach this. Can you provide any sample code ? It would help me understand far better.

I appreciate your help.

_{Monday, August 24, 2015 11:38 PM}

Thanks for the reply Linda, this worked; however now my list view is cramped with 2 extra fields that don't always have data in them.

Can you suggest anything to hide field B when it is blank ?

_{Wednesday, August 26, 2015 1:34 AM}

Thank you very much Linda, I really appreciate your detailed instructions. This has helped me to further my knowledge.

One last thing, I'm using Sharepoint Designer 2010 so I do not have 'transition to stage' at the end of the workflow. What would be the 2010 equivalent?

_{Wednesday, August 26, 2015 3:16 AM | 1 vote}

Hi Jasmine,

If you use the Workflow 2010, you can reference the screenshot below:

Best regards,

Linda Zhang