Combining IF statements
Hi Smartsheet community,
I am currently trying to create a formula, that states if Updated Completed Date is blank then use the date in Original Completion Date column but if not blank then use the date in the Updated Completed Date column to return quarter and year (Q4 2020). I am creating this formula in the Estimated Completion Quarter column. Any help will be greatly appreciated. Thanks!
Best Answer
-
Hi @Angelique1,
I think you would need a helper column for that. The helper column would be something like this:
If(MONTH([updated completed date]@row)<4, "Q1 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>3<7, "Q2 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>6<10, "Q3 " + YEAR([updated completed date]@row), "Q4 " + YEAR([updated completed date]@row)
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
Answers
-
Hi,
A basic IF() formula should work for what you're looking for, something like this:
=IF([Updated Completed Date (FCI)]@row = "", [Original Completion Date (FCI)]@row, [Updated Completed Date (FCI)]@row)
Hope this helps! Let me know if you have any questions.
Best,
Mike
-
Hi @Angelique1
Try something like this, using your real column names.
=If(not(isblank([updated completed date]@row)), [original completed date]@row, [updated completed date]@row)
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
Thank you both! Is there any way to have the output read as quarter and year for example: Q1 2021, Q2 2021, Q1 2022, etc.?
-
Hi @Angelique1,
I think you would need a helper column for that. The helper column would be something like this:
If(MONTH([updated completed date]@row)<4, "Q1 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>3<7, "Q2 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>6<10, "Q3 " + YEAR([updated completed date]@row), "Q4 " + YEAR([updated completed date]@row)
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!