Academic Calendar: How to automate dates using days of the week

anaMG

Hello everyone!

I'm trying to get automatically the dates for each lesson, based on the first class (orientation). Days of the week are fixed.

Is there a way to calculate all dates for classes and AWs based on the Orientation Date?

Thank you very much in advance!

Paul McGuinness

Hi @anaMG

You have multiple formulas running here, most are straightforward but they all rely on the first one being correct (blue formula).

You don't say if orientation happens on a specific day of the week, or if classes / AWs can happen in the same week as orientation. So assumed they can.

I've broken it down as below, the formula in blue is the key, getting that correct allows all the other formulas to refer to it and calculate simply. This formula will work for orientation on any day of the week, with classes happening in the same week as orientation if that date is Monday, Tuesday or Wednesday.

The example below shows orientation happening on a Wednesday.

The formulas in green simply refer to that first blue cell and add or subtract the required number of days to deliver the respective days date.

Here is the blue formula for copy n pasting:

=IF(WEEKDAY(Date2) = 1, Date2 + 4, IF(WEEKDAY(Date2) = 2, Date2 + 3, IF(WEEKDAY(Date2) = 3, Date2 + 2, IF(WEEKDAY(Date2) = 4, Date2 + 1, IF(WEEKDAY(Date2) = 5, Date2 + 7, IF(WEEKDAY(Date2) = 6, Date2 + 6, IF(WEEKDAY(Date2) = 7, Date2 + 5, "Error")))))))

Hope that helps

Thanks

Paul

anaMG

To calculate the first Thursday after the "Orientation Date" I found this formula:

=Date2 + 7 - WEEKDAY(Date2 + 2)

This works, but I don't understand it, why is it adding 7 and subtracting "WEEKDAY(Date2 + 2)"

I want to understand this to be able to develop the rest of the formulas I need.

Thank you all!

Paul McGuinness

Hi @anaMG

You have multiple formulas running here, most are straightforward but they all rely on the first one being correct (blue formula).

You don't say if orientation happens on a specific day of the week, or if classes / AWs can happen in the same week as orientation. So assumed they can.

I've broken it down as below, the formula in blue is the key, getting that correct allows all the other formulas to refer to it and calculate simply. This formula will work for orientation on any day of the week, with classes happening in the same week as orientation if that date is Monday, Tuesday or Wednesday.

The example below shows orientation happening on a Wednesday.

The formulas in green simply refer to that first blue cell and add or subtract the required number of days to deliver the respective days date.

Here is the blue formula for copy n pasting:

=IF(WEEKDAY(Date2) = 1, Date2 + 4, IF(WEEKDAY(Date2) = 2, Date2 + 3, IF(WEEKDAY(Date2) = 3, Date2 + 2, IF(WEEKDAY(Date2) = 4, Date2 + 1, IF(WEEKDAY(Date2) = 5, Date2 + 7, IF(WEEKDAY(Date2) = 6, Date2 + 6, IF(WEEKDAY(Date2) = 7, Date2 + 5, "Error")))))))

Hope that helps

Thanks

Paul

anaMG

Thank you so much Paul!! This is what I needed!