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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!