Academic Calendar: How to automate dates using days of the week
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!
Best Answer

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
Answers

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!

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

Thank you so much Paul!! This is what I needed!
Help Article Resources
Categories
Check out the Formula Handbook template!