Forum Discussion

klrsgis's avatar
klrsgis
Copper Contributor
Jul 12, 2023

Sharepoint List Field Calculation with Dates: IF Statements with 3 conditions

Hello! 

I need help adding a condition to my field calculation for calcuating the expiration date of a permit. I currently populate an expiry field based on two columns: [Assessment Type] and [Date Approved].

  • [Assessment Type] is a choice field with two choices
  • Single Assessment = 1 year expiry from date of approval and Multiple Assessment = 2 year expiry from date of approval. 
  • Pseudo: If a permit is a Multiple Assessment Permit, increment 2 years from date of approval, otherwise increment by 1 year
  • =IF([Assessment Type]="Multiple Assessment",DATE(YEAR([Date Approved])+2,MONTH([Date Approved]),DAY([Date Approved])),DATE(YEAR([Date Approved])+1,MONTH([Date Approved]),DAY([Date Approved])))

 

I create a new row in my sharepoint list everytime I recieve a permit application, it has multiple columns that relate to the permit. Project Name, company, important dates etc. Our company has now expanded our permits to accept 6-month Extensions. I want to be able to log in a new entry for the extension request but have the expiry date reflect the 6-month extension from the date of approval. The date I approve an extension is different from when I approved the original permit, because obviously the request comes after the fact.

 

For example, I could have a row in my list for Project A, a single assesment permit, that was approved on 7/12/2022. Currently my expiry date field would populate to 7/12/2023, by calculation. But I recieve a 6-month extension request 7/5/2023. I log all the pertinent information for the extension request and approve the extension request on 7/12/2023. But instead of having the [Permit Expiry] calculate 1 additional year from date of approval, I need it to calculate +6 months from the date of approval if the [App Submission] field is populated with "6-Month Extension".

 

Details of what I set-up for the new permit applications:

  • [App Submission] is a look-up column I recently created to try and log the permit submission being requested
  • This look up includes: Amendment, 6-Month Extension, Single Assessment Renewal and Multiple Assessment Renewal
  • I want the PERMIT EXPIRY field to calculate 6 additional months from the date of approval, if the [App Submission] field is logged as "6-month Extension" 

It might be easier if I have a seperate field for Extension Expiry dates? But I am wondering if I can make this work? 

 

Thank you so much for your time if you look at this post. 

No RepliesBe the first to reply

Resources