Forum Discussion
Looking for some guidance on a calculated column with multiple IF statements
Hello Sharepoint wizards!
I am trying to create a calculated column that shows an end date for a class based on:
Column 1: Title (name of person)
- Manually entered at time of list item creation
Column 2: Start date (Date column)
- Manually selected at time of list item creation
Column 3: Class type (Choice column)
- “Math” (a class that takes 30 days)
- “English” (a class that takes 30 days)
- “Science” (a class that takes 45 days)
- “History” (a class that takes 45 days)
Column 4: Calculated end date (Date Column)
- Start date + 30 or 45 days based on what is selected in Column 3
So in layman’s syntax it would go something like:
IF Column 3 =“Math” or “English”, then (Column 2 + 30), or IF Column 3 = “Science” or “History”, then (Column 2 + 45)
But no matter how I try and format the actual formula, it won’t accept it. And now it has me thinking that the IF function isn’t even what I’m suppose to use and it’s driving me BANANAS! So here I am coming to the Sharepoint gods for help. Many many thanks in advance. Many many many thanks.
You can try below formula:
=IF(OR(ISBLANK([Class type]),ISBLANK([Start date])),"",IF(OR([Class type] = "Math",[Class type] = "English"),[Start date]+30,[Start date]+45))I have also considered scenario in above formula i.e. if Class type or Start date are blank then end date will be empty. See output in below screen shot:
Make sure that you are using correct internal name of both the column in formula. Get hint from this link if you don't know how to find internal name of the column
Official Documentation: Calculated Field Formulas
Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community
2 Replies
You can try below formula:
=IF(OR(ISBLANK([Class type]),ISBLANK([Start date])),"",IF(OR([Class type] = "Math",[Class type] = "English"),[Start date]+30,[Start date]+45))I have also considered scenario in above formula i.e. if Class type or Start date are blank then end date will be empty. See output in below screen shot:
Make sure that you are using correct internal name of both the column in formula. Get hint from this link if you don't know how to find internal name of the column
Official Documentation: Calculated Field Formulas
Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community
- Ahhhh this is great. I would not have figured this out organically, but your answer provides me a better understanding about how to solve for this situation in the future.
Thank you very much! What an amazing community!
