How to add time or subtract time from a date when "check" is selected?
Hi!
Looking to see if this is possible. I have a packaging release date column that current pulls from another sheet for all items. I have items for Disney that I need to subtract 30 days when I click Disney checkbox column. When I uncheck the checkbox column, I want the date to revert back or add 30 days back.
Here is the current formula for the packaging release date is
=IFERROR(INDEX({WF PKG Release}, MATCH([REFERENCE NUMBER]@row, {Reference number Range}, 0)), "")
I'll then have a new checkbox column to trigger the 30 days.
Work Smarter, Not Harder
Answers
-
Can you try this:
=IF(Checkbox_Column@Row = 1 , IFERROR(INDEX({WF PKG Release}, MATCH([REFERENCE NUMBER]@row, {Reference number Range}, 0) + 30), ""), IFERROR(INDEX({WF PKG Release}, MATCH([REFERENCE NUMBER]@row, {Reference number Range}, 0)), ""))...
-
@heyjay THANK YOU! Date changes but is not accurate.
Work Smarter, Not Harder
-
@heyjay Ok I need a subtraction. I tried:
=IF(Checkbox_Column@Row = 1 ,IFERROR(INDEX({WF PKG Release}, MATCH([REFERENCE NUMBER]@row, {Reference number Range}, 0) - 30), ""), IFERROR(INDEX({WF PKG Release}, MATCH([REFERENCE NUMBER]@row, {Reference number Range}, 0)), ""))
But the date is subtracting by 3 months.
Work Smarter, Not Harder
-
Your formula seems correct. May I know what date are you using and what date is showing when you use the formula.
...
-
Before check
Checked off. Date is not 30 days before.
Formula
Work Smarter, Not Harder
-
Your -30 is in the wrong spot. Try this one instead:
=IFERROR(INDEX({WF PKG Release}, MATCH([REFERENCE NUMBER]@row, {Reference number Range}, 0)) - IF([Column Name]@row = 1, 30, 0), "")
-
@Paul Newcome You the man!!! That worked! Thank you!
Work Smarter, Not Harder
-
Happy to help. 👍️
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!