Solution for Creating a Formula that Utilizes Two Separate Columns As Conditions For The Output of a Calculated Column

Question

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?

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:

  

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

  

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.