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.

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

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?

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.

• Edited by Jasmine P 8 hours 0 minutes ago

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 ?

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.

• Edited by Jasmine P Monday, August 24, 2015 11:38 PM

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

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

• Marked as answer by Jasmine P 3 hours 42 minutes ago

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?

Hi Jasmine,

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

Best regards,

Linda Zhang