IF statements and checkboxes
Hi there,
Currently I'm using this formula to check boxes in a column if the date a proposal is sent is in 2019.
IF(AND([Date Proposal Sent]534 > DATE(2018, 12, 31), [Date Proposal Sent]534 < DATE(2019, 12, 31))
Sometimes there is no date value for "Date Proposal Sent" and the cell says "In Pending". This causes my checkboxes to say "Invalid Operation". How can I revise my formula so that if the "Date Proposal Sent" is "In Pending" that the checkbox is simply not marked off?
Thanks in advance!
Mahshad
Best Answer
-
Hi Mahshad,
Try something like this.
=IF(ISTEXT([Date Proposal Sent]@row); 0; IF(AND([Date Proposal Sent]@row > DATE(2018; 12; 31); [Date Proposal Sent]@row < DATE(2019; 12; 31)); 1; 0))
The same version but with the below changes for your and others convenience.
=IF(ISTEXT([Date Proposal Sent]@row), 0, IF(AND([Date Proposal Sent]@row > DATE(2018, 12, 31), [Date Proposal Sent]@row < DATE(2019, 12, 31)), 1, 0))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."
Did that work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi Mahshad,
Try something like this.
=IF(ISTEXT([Date Proposal Sent]@row); 0; IF(AND([Date Proposal Sent]@row > DATE(2018; 12; 31); [Date Proposal Sent]@row < DATE(2019; 12; 31)); 1; 0))
The same version but with the below changes for your and others convenience.
=IF(ISTEXT([Date Proposal Sent]@row), 0, IF(AND([Date Proposal Sent]@row > DATE(2018, 12, 31), [Date Proposal Sent]@row < DATE(2019, 12, 31)), 1, 0))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."
Did that work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
You can also use:
=IFERROR(IF(YEAR([Date Proposal Sent]@row) = 2019, 1), 0)
Basically we just use the YEAR function to pull the year from the date instead of having to specify a date range.
=IF(YEAR([Date Proposal Sent]@row) = 2019, 1)
Then to account for no date being present throwing an error, we just wrap it in an IFERROR statement to replace the error with a 0.
=IFERROR(IF(YEAR([Date Proposal Sent]@row) = 2019, 1), 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!