Microsoft Lists - compress formula to minimize character

Anonymous
2025-06-15T20:07:53+00:00

I am using the following to calculate the Due Date (Ddate) for items in a Microsoft List. I understand there is a limit to 1024 characters. However, the formula currently only works through Weeks 6, then I get an error if I try to add any additional characters to the formula. I really need the formula to calculate Ddate for the "76" options. How is the best way to accomplish this? Any assistance would be GREATLY APPRECIATED.

Priority "1-14 days" before state date, Priority "3-Weeks" and beyond is of course 64 weeks, prior to the start date.

=IF(Priority="1-Day",Sdate-1,IF(Priority="2-Days",Sdate-2,IF(Priority="3-Days",Sdate-3,IF(Priority="4-Days",Sdate-4,IF(Priority="5-Days",Sdate-5,IF(Priority="6-Days",Sdate-6,IF(Priority="7-Days",Sdate-7,IF(Priority="8-Days",Sdate-8,IF(Priority="9-Days",Sdate-9,IF(Priority="10-Days",Sdate-10,IF(Priority="11-Days",Sdate-11,IF(Priority="12-Days",Sdate-12,IF(Priority="13-Days",Sdate-13,IF(Priority="14-Days",Sdate-14,IF(Priority="3-Weeks",Sdate-21,IF(Priority="4-Weeks",Sdate-28,IF(Priority="5-Weeks",Sdate-35,IF(Priority="6-Weeks",Sdate-42,IF(Priority="7-Weeks",Sdate-49,IF(Priority="8-Weeks",Sdate-56,IF(Priority="9-Weeks",Sdate-63,IF(Priority="10-Weeks,Sdate-70,IF(Priority="11-Weeks,Sdate-77,IF(Priority="12-Weeks,Sdate-84,IF(Priority="13-Weeks,Sdate-91,IF(Priority="14-Weeks,Sdate-98,IF(Priority="15-Weeks,Sdate-105,IF(Priority="16-Weeks,Sdate-112,IF(Priority="17-Weeks,Sdate-119,IF(Priority="18-Weeks,Sdate-126,IF(Priority="19-Weeks,Sdate-133,IF(Priority="20-Weeks,Sdate-140,IF(Priority="21-Weeks,Sdate-147,IF(Priority="22-Weeks,Sdate-154,IF(Priority="23-Weeks,Sdate-161,IF(Priority="24-Weeks,Sdate-168,IF(Priority="25-Weeks,Sdate-175,IF(Priority="26-Weeks,Sdate-182,IF(Priority="27-Weeks,Sdate-189,IF(Priority="28-Weeks,Sdate-196,IF(Priority="29-Weeks,Sdate-203,IF(Priority="30-Weeks,Sdate-210,IF(Priority="31-Weeks,Sdate-217,IF(Priority="32-Weeks,Sdate-224,IF(Priority="33-Weeks,Sdate-231,IF(Priority="34-Weeks,Sdate-238,IF(Priority="35-Weeks,Sdate-245,IF(Priority="36-Weeks,Sdate-252,IF(Priority="37-Weeks,Sdate-259,IF(Priority="38-Weeks,Sdate-266,IF(Priority="39-Weeks,Sdate-273,IF(Priority="40-Weeks,Sdate-280,IF(Priority="41-Weeks,Sdate-287,IF(Priority="42-Weeks,Sdate-294,IF(Priority="43-Weeks,Sdate-301,IF(Priority="44-Weeks,Sdate-308,IF(Priority="45-Weeks,Sdate-315,IF(Priority="46-Weeks,Sdate-322,IF(Priority="47-Weeks,Sdate-329,IF(Priority="48-Weeks,Sdate-336,IF(Priority="49-Weeks,Sdate-343,IF(Priority="50-Weeks,Sdate-350,IF(Priority="51-Weeks,Sdate-357,IF(Priority="52-Weeks,Sdate-364,IF(Priority="53-Weeks,Sdate-371,IF(Priority="54-Weeks,Sdate-378,IF(Priority="55-Weeks,Sdate-385,IF(Priority="56-Weeks,Sdate-392,IF(Priority="57-Weeks,Sdate-399,IF(Priority="58-Weeks,Sdate-406,IF(Priority="59-Weeks,Sdate-413,IF(Priority="60-Weeks,Sdate-420,IF(Priority="61-Weeks,Sdate-427,IF(Priority="62-Weeks,Sdate-434,IF(Priority="63-Weeks,Sdate-441,IF(Priority="64-Weeks,Sdate-448,)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Microsoft 365 and Office | SharePoint | For education | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2025-06-18T11:51:33+00:00

Hi BLBARBER, 

Great to hear the issue resolved. If you think the answer is helpful, please mark it as an answer, which will definitely help others in the community who have similar queries to find solutions to their problems faster.  

 

Thank you for your valuable feedback, glad to know that my answer helpful. I appreciate your kind words. 
Warmest regards, 

Vicky-I - MSFT | Microsoft Community Support Specialist

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2025-06-17T15:38:30+00:00

Hi BLBARBER, 

Thank you for your update! I tried to research and here’s solution for your error: 

  1. Syntax Error Identified: 
    1. Your formula appears to have inconsistent spacing, and line breaks that SharePoint doesn't recognize. Calculated columns require continuous single-line formulas without line breaks.
  2. Corrected Formula:

=[Sdate] - IF(ISNUMBER(SEARCH("-Days", [Priority])), VALUE(LEFT([Priority], SEARCH("-", [Priority]) - 1)), IF(ISNUMBER(SEARCH("-Weeks", [Priority])), VALUE(LEFT([Priority], SEARCH("-", [Priority]) - 1)) * 7, 0)) 

  1. Validation Steps: 
    1. Check Column Names: Verify Sdate and Priority match exact internal names (go to column settings > check "Name" field).
    2. Test Simple Cases First: Try a basic formula like =[Sdate] - 1 to confirm the column works, then add complexity.
    3. Date Format: Ensure Sdate is a valid date column (your output type "Date Only" is correct).
  2. Common Pitfalls: 
    1. Text vs. Numbers: LEFT() returns text—use VALUE() to convert to a number.
    2. Hyphen Logic: If Priority uses formats like "5-Days" or "2-Weeks", the formula should work. For other formats (e.g., "High"), it defaults to 0.
  3. Next Steps: 
    1. Copy the corrected formula above exactly into your calculated column.
    2. If it fails again, share a sample value of Priority and Sdate for further debugging.

If the error persists, I strongly recommend you submit a Support Ticket (Your IT admin can escalate this to Microsoft via) https://admin.microsoft.com/#/support/requests for further and deeper step by step assistance. 

Warmest regards, 

Vicky-I -MSFT | Microsoft Community Support Specialist

Was this answer helpful?

0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-06-26T12:51:16+00:00

    Works perfectly. Thanks so very much for your assistance.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Anonymous
    2025-06-15T22:52:29+00:00

    Dear BLBARBER, 

    Thank you for your detailed question. I see you have issues with the character limit in your complex due date calculation. Let me help you optimize this. To overcome the character limit while maintaining all 76 priority levels, here’s a more efficient approach using mathematical parsing instead of nested IFs: 

    Optimized Formula Solution: 

    =Sdate -  
    IF( 
       ISNUMBER(SEARCH("-Days", Priority)), 
       VALUE(LEFT(Priority, SEARCH("-", Priority) - 1)), 
       IF( 
           ISNUMBER(SEARCH("-Weeks", Priority)), 
           VALUE(LEFT(Priority, SEARCH("-", Priority) - 1)) * 7, 
           0  // Default case 
       ) 
    ) 

    This will work like: 

    1. For Day Priorities (1-14 Days)  
      1. Extracts the number before "-Days"
      2. Subtracts that value directly (e.g., "3-Days" → 3)
    2. For Week Priorities (3-64 Weeks)  
      1. Extracts the number before "-Weeks"
      2. Multiplies by 7 (e.g., "5-Weeks" → 5×7 = 35)

    And here is how to perform the above steps: 

    1. Create a Calculated Column in your Microsoft List
    2. Paste this formula (only 380 characters!)
    3. Test with:  
      1. Priority = "6-Days" → Returns Sdate-6
      2. Priority = "12-Weeks" → Returns Sdate-84

    For education-specific implementations, please consider creating a lookup table if priorities change frequently and use Power Automate for complex business logic beyond formulas. 

    If we appreciate your time and look forward to resolving this for you. Feel free to ask me if the issue still persists! 

    Best regards, 

    Dustin-TP - MSFT | Microsoft Community Support Specialist.

    Was this answer helpful?

    0 comments