question

JamesSnider-1900 avatar image
0 Votes"
JamesSnider-1900 asked CaseyYangMSFT-4714 commented

Calculated Date Field Based on Other Fields

I'm trying to calculate a due date field based on two other fields in a SharePoint Online list: [Created] and [Priority]. [Created] is auto-populated when the user creates the new list item. Easy enough. The user selects a [Priority] (Critical, High, Medium, Low). My formula always results in an error due to the pre-populated date field. Surely there's a way around this that I'm just ignorant of. It works fine in Excel, but SP pukes when I try it. TIA!

 =IF([Priority]="Critical",[Created]+1,IF([Priority]="High",[Created]+3,IF([Priority]="Medium",[Created]+7,[Created]+14)))
office-sharepoint-online
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CaseyYangMSFT-4714 avatar image
0 Votes"
CaseyYangMSFT-4714 answered CaseyYangMSFT-4714 commented

Hi @JamesSnider-1900,

Per my test, you could add a new calculated column with your formula. Remember to select "Date and Time" in "The data type returned from this formula is".

114101-1.png

Here is my test result.

114092-2.png


If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



1.png (33.9 KiB)
2.png (12.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JamesSnider-1900,

I'm checking how the things are going on about this issue. Is there any progress on this issue?

0 Votes 0 ·
JamesSnider-1900 avatar image
0 Votes"
JamesSnider-1900 answered CaseyYangMSFT-4714 commented

Hi @CaseyYangMSFT-4714

Thanks for your reply! However, I'm still hitting the same error wall. I've included a screenshot of my calculated field settings and the error I get. The Create Date is auto populated when the list item is made. I want to calculate the Due Date based on Create Date + Priority.
Neither of these formulas worked: =IF([Priority]="Critical",[Created]+1,IF([Priority]="High",[Created]+3,IF([Priority]="Medium",[Created]+7,[Created]+14)))

 =IF(Priority="Critical",Created+1,IF(Priority="High",Created+3,IF(Priority="Medium",Created+7,Created+14)))

114219-fieldsettings.png

114264-error.png





fieldsettings.png (34.6 KiB)
error.png (19.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JamesSnider-1900,

Like my answer said, you could add a "Due Date" calculated column with your formula not a Date and time column. You could use something like WEEKDAY function in Calculated Value, but you could not use other fields. In my answer the "create date" column works just like you said "based on Create Date + Priority". Sorry for the wrong name, I should rename it "Due Date".



0 Votes 0 ·