Trying to write a formula that calculates the expected completion date based on the priority of the project day it was assigned.

For two of the priorities, it needs to be a text response in the calculated column. Current formula as follows:

=if([Priority]="Critical"),[Delivery Date] + 10, if([Priority]="High"), [Delivery Date] + 25, if([Priority]="Medium", [Delivery Date] + 65, if([Priority]="Low", "Next major version", if([Priority]="Informational", "Informational"))))

I'm trying to say, if priority is high, add 10 days to the delivery date (delivery date is when it was assigned). I want to know the due date. Since there is text in the formula, how do I get this to show as a text field, provide the dates for the 3 priority items (Critical, High, Medium) that need dates and generate the text for low and informational?

1 Answer 1


You will have to convert the date values into text, you can use the TEXT(Date, Format) function for that.

You also had some parentheses on the loose, it helps to type the formulas out like this:

=if([Priority]="Critical", TEXT([Delivery Date] + 10,"yyyy-mm-dd"), 
    if([Priority]="High", TEXT([Delivery Date] + 25,"yyyy-mm-dd"), 
        if([Priority]="Medium", TEXT([Delivery Date] + 65,"yyyy-mm-dd"), 
            if([Priority]="Low", "Next major version", 
                if([Priority]="Branch E", "Informational","Unexpected Value")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.