Calculate End Date based on checkbox columns?

Cara Graf

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!

Paul Newcome

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.

Paul Newcome

Would it be the Friday before or the Friday after?

Cara Graf

@Paul Newcome it would be the Friday after!

Paul Newcome

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.

Cara Graf

Thank you, @Paul Newcome! Can always count on you to come through!

Paul Newcome

Happy to help. 👍️