Hi SharePoint experts,

I have two columns in a Sharepoint List, one titled "Budgeted Amount" and the other "Contract Expiration Date".

What I'm trying to do is create a calculated column that outputs a date depending on value that's in "Budgeted Amount."

Here's the criteria: If "Budgeted Amount" is over 1,000,000 then output the date 180 days before the "Contract Expiration Date" If "Budgeted Amount" is between 250,000 and 999,999 then output the date 90 days before the "Contract Expiration Date" If "Budgeted Amount" is between 100,000 and 249,999 then output the date 30 days before the "Contract Expiration Date".

I do NOT get any syntax errors, but every date that populates in my calculated column is 180 days before or 0. Here's the code I've tried:

=IF(AND([Budgeted Amount]>=100000,[Budgeted Amount]<=249999),[Contract Expiration Date]-30,
IF(AND([Budgeted Amount]>=250000,[Budgeted Amount]<=999999),[Contract Expiration Date]-90,
IF([Budgeted Amount]>=1000000,[Contract Expiration Date]-180)))

I've also tried this even though this would populate a date for items under 100,000:

=IF( [Budgeted Amount]<=249999
     ,[Contract Expiration Date]-30
     ,IF( [Budgeted Amount]<=999999
               ,[Contract Expiration Date]-90
               ,[Contract Expiration Date]-180     

I feel like I'm missing something here. Any suggestions?

  • Check if this article helps you: tracyvanderschyff.com/2016/12/01/… It allows you to add/subtract days from the date but you need to check your date type field.
    – Pradip R.
    Commented Aug 1, 2023 at 22:39
  • For me your last expression given me exact date as per your query, so I would ask you to check your column types. Your Budget Amount has to be of type number or currency and your Contract Expiration Date has to be Date Only Date Time field.
    – Pradip R.
    Commented Aug 1, 2023 at 23:10

1 Answer 1


Based on my findings, it appears that the following elements might be missing:

  • Please ensure that your Budget Amount field type is set to either Currency or Number. Both options will work, but it is recommended to use the Currency type for ideal consistency. In some cases, you might not have the option to update the field type, so I recommend considering the Int method as explained in Microsoft article:

    enter image description here

  • Additionally, verify that your Contract Expiration Date field is set to either Date Only in Date and Time field.

    enter image description here

In my list, there is a calculated column named Reminder Date with the following formula:

=IF([BudgetAmount] >= 1000000, [ContractExpirationDate] - 180,
    IF([BudgetAmount] >= 250000, [ContractExpirationDate] - 90,
        IF([BudgetAmount] >= 100000, [ContractExpirationDate] - 30,

and it generates a Reminder Date based on the Budget Amount and Contract Expiration Date values that you have specified in your requirement.

enter image description here

  • You were totally right about the column data type. I'm not the only who has access to the sharepoint list and someone changed the currency data column back to single line of text. Thank you!
    – Ryan
    Commented Aug 2, 2023 at 19:25
  • Glad it worked for you!
    – Pradip R.
    Commented Aug 2, 2023 at 20:13

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.