Automatic Checkbox Based on 3 Sets of Dates
I'm trying to get a checkbox to check automatically if the day in progress is within a given month, but has not been completed in the same month and has not been placed on hold after the day in the month that it was placed in progress. Any help would be grateful!!
If the project was dated as "In Progress" on 6/15/2022 (helper date column already created for date in progress)
Completed date was not in June or before June (helper date column already created for date completed)
On Hold date is not equal to 6/15/2022 or between 6/15/2022 and 6/30/2022 (helper date column already created for date on hold)
Answers
-
Please provide the name of the columns in question. Too often people cannot get formulas to work if they can't just copy-paste them into their sheet from the forums.
-
I created 2 checkbox columns for each month labeled In Progress and On Hold (i.e. Jan'22 In Progress, Jan'22 On Hold). I then have 3 date columns, one for when the project was put into the In Progress, On Hold and my date completed (using the automation to record a date).
Date Completed Date In Progress Date On Hold Jan'22 In progress (checkbox) Jan'22 On Hold (checkbox)
-
Are you able to provide some screenshots?
-
-
I'm trying to get the "On Hold/Declined In September" checkbox to check if the date is in the same month as In Progress, but the day is between 9/26 and 9/30.
-
So to do this specifically for September 2022 you would use a formula such as this:
=IF([Date of Status Change (On Hold/Declined)]@row <> "", IF(AND([Date of Status Change (On Hold/Declined)]@row>= [Date of Status Change (In Progress)]@row, [Date of Status Change (On Hold/Declined)]@row<= DATE(2022, 09, 30)), 1))
-
That works great for the On Hold/Declined! How would I write the first IF statement for the In Progress checkboxes?
-
You would use something like this...
=IF([Date of Status Change (In Progress)]@row <> "", IF(AND(MONTH([Date of Status Change (In Progress)]@row) = 9, YEAR([Date of Status Change (In Progress)]@row) = 2022), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!