IF Formula
Hello,
I am struggling for while creating one formula.
In my Smartsheet I have Column "Status" with different statuses from a dropdown. One of them is Completed. I also have a Column "End Date" where different dates are being selected.
I want to create a Column "Last 14 days" and one "Next 14 Days" for a report. I need a formula that automatically fills in this columns with yes or no.
In "Last 14 days": Display Yes, if Status =Completed and End Date lies in the last 14 days.
In "Next 14 days": Display Yes, if Status =Completed and End Date lies in the next 14 days.
I am trying this but it does not work for me.
=IF(AND(Status = "Completed", End Date =< TODAY (-14)()), [Last 14 days], yes, no)
=IF(AND(Status = "Completed", End Date =< TODAY (+14)()), [Last 14 days], yes, no)
I hope someone can help.
Thanks
Veli
Best Answer
-
You do not need to replace @row with anything. You can leave it as is. Try changing the commas to semi-colons.
Answers
-
Try these...
=IF(AND(Status = "Completed", End Date => TODAY (-14)), [Last 14 days], yes, no)
=IF(AND(Status = "Completed", End Date =< TODAY (14)), [Last 14 days], yes, no)
-
Still no success. When I copy paste in the rows of the Last 14 days Column , it states #Unparseable. Anyway thanks for the quick answer.
-
My apologies. I misread your original formulas.
Last 14 Days:
=IF(AND(Status = "Completed", [End Date]@row >= TODAY (-14), [End Date]@row <= TODAY()), "yes", "no")
Next 14 Days:
=IF(AND(Status = "Completed", [End Date]@row <= TODAY (14), [End Date]@row >= TODAY()), "yes", "no")
-
Hi Paul,
Where it says @row do I replace that with just the number of that row? Like this:
=IF(AND(Status = "Completed", [End Date]1 >= TODAY (-14), [End Date]1 <= TODAY()), "yes", "no")
If yes this still does not work for me. I paste the above in a cell at the end on the row 1 & it displays again #Unparseable, instead yes or no.
Regards
Veli
-
You do not need to replace @row with anything. You can leave it as is. Try changing the commas to semi-colons.
-
Thanks a lot Paul for the help. Now it works. I also exchanged Status = with Status@row
-
Sorry for missing that @row on Status. Glad you were able to get it working. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!