Calculate End Date based on checkbox columns?
Hello,
I have a sheet where I keep track of student start/end dates. Currently, I have a column formula in my "End Date" column that will calculate 6 months after the start date AND make it a Friday.
We now offer programs with multiple lengths: 90 days, 120 days, or 6 months. I feel like there is an easy way for me to continue calculating end dates (and placing them on a Friday) by using helper columns, but I'm stuck on how to make it work.
My idea is to have 3 checkbox columns right before my "Start Date" column where I can select if this student is enrolled in a 90 day, 120 day, or 6 month program, then have an IF statement for my "End Date" column that says something like IF 90 days is checked, make the End Date 90 days past the Start Date and end on a Friday.
Any guidance is greatly appreciated!
editing to add: I figured out the first part of my formula! I have my checkbox columns named "90 day program", "120 day program", and "6 month program" with a "Start Date" and "End Date" column. I was able to get the End Date column to calculate correctly based off using my checkboxes with this formula: =IF([6 month program]@row, [start date]@row + 180, IF([90 day program]@row, [start date]@row + 90, IF([120 day program]@row, [start date]@row + 120)))
Now I just need to figure out how to make those dates be a Friday!
Best Answer
-
Ok. In that case you would use this as the base:
=Date@row + (6 - WEEKDAY(Date@row)) + IF(WEEKDAY(Date@row) = 7, 7, 0)
You can either replace "Date@row" with your existing formula (all 3 instances) or you can use a helper column to reference the cell your existing formula is currently residing in. This is also assuming that if the date falls on a Friday, you want to maintain that date and not go out to the next Friday.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Would it be the Friday before or the Friday after?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome it would be the Friday after!
-
Ok. In that case you would use this as the base:
=Date@row + (6 - WEEKDAY(Date@row)) + IF(WEEKDAY(Date@row) = 7, 7, 0)
You can either replace "Date@row" with your existing formula (all 3 instances) or you can use a helper column to reference the cell your existing formula is currently residing in. This is also assuming that if the date falls on a Friday, you want to maintain that date and not go out to the next Friday.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, @Paul Newcome! Can always count on you to come through!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives