Microsoft Lists - Calculated Column - Formula doesn't work

Question

Hi,

I have a list with calculated column called "nxtDate", the logic is "nxtDate" = "Date reported" + "Cycle", the number in column "Cycle" means number of years.

The following formula I set in nxtDate does not work and I received an error (actually the formula is coming from this page

https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3).

Then I can only use "=[Date reported]+365*[Cycle]" and it works.. but sometimes one year contains 366 days, so the formula is not so accurate.

Answer 1

Hi Youwei_767,

Thank you for sharing the experience with us. From the information you provided, it seems the issue happens when you are trying to create a calculated column in an issue tracker list.

When I test from my side, use the same formula as below for a calculated column, the column is successfully created.

=DATE(YEAR([Date reported])+[Cycle],MONTH([Date reported]),DAY([Date reported]))

For troubleshooting, you may try following ways:

1.Create a new calculated column in the same SharePoint list and check if the formula works.

2.Create a new SharePoint issue tracker list and try to create a calculated column, see if the formula works.

3.To narrow down if issue only appears in a specific SharePoint site, you may also use a different SharePoint site and check the result.

Appreciate your time and effort on this issue.

Merry Christmas!

Best Regards,

Tina

Answer 2

Hi Tina,

Merry Christmas and thank for your reply.

I created another list and entered the formula like below:

=DATE(YEAR([Last Calibration Date])+[Calibration Cycle],MONTH([Last Calibration Date]),DAY([Last Calibration Date]))

but I still received an error saying that "The formula contains a syntax error or is not supported."

Then I removed "+[Calibration Cycle]" portion, and change all "," to ";" like below, then I found it became OK.

=DATE(YEAR([Last Calibration Date]);MONTH([Last Calibration Date]);DAY([Last Calibration Date]))

however, when I checked the column setting and I found that the words of the formula has been converted to Swedish, I realized that this sharepoint site was created by the admin using Swedish version Windows.

Then I tried the following

=DATUM(ÅR([Last Calibration Date])+[Calibration Cycle];MÅNAD([Last Calibration Date]);DAG([Last Calibration Date]))

but this time the error I received is "The formula refers to a column that does not exist.  Check the formula for spelling mistakes or change the non-existing column to an existing column."

Answer 3

Hi Youwei_767,

Thank you for spending time doing the test and posting back to let us know the result.

I test the following formula that you mentioned from my side, I still don't encounter any issue when I try to use it in a calculated column.

=DATE(YEAR([Last Calibration Date])+[Calibration Cycle],MONTH([Last Calibration Date]),DAY([Last Calibration Date]))

To check if the issue only happens in a specific SharePoint site, I suggest you try to create a list in a different SharePoint site and check if you still get any error message.

Thank for your effort. Have a nice day.😊

Best Regards,

Tina

Answer 4

Hi Youwei_767,

Are you still experiencing issue when creating calculated column? Let us know if you need further help.

Best Regards,

Tina