How to fill in dates based on previous dates

[abuchanan]

This should be an easy fix, but I don't know how to put into visual basic (a lot of the syntax is confusing):

I have the following original file for a school offering classes on various days / times. One line = a specific class session.
Each class will have a different number of sessions for the month of August; i.e. HR Law may meet on Tues and Thurs for three weeks at 6P for a total of 6 session; another class, Introduction to HR may meet on Wed at 5p to 9p for only one day.

Date	Start Time	End Time	Class	Session
Saturday, August 1, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 1
Tuesday, August 4, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 1
Wednesday, August 5, 2020​	1:00:00 PM​	3:00:00 PM​	Class 3	Session 1
Wednesday, August 5, 2020​	6:30:00 PM​	8:00:00 PM​	Class 4	Session 1
Thursday, August 6, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 2
Saturday, August 8, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 2
Monday, August 10, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 1
Tuesday, August 11, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 1
Tuesday, August 11, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 3
Wednesday, August 12, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 2
Thursday, August 13, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 2
Thursday, August 13, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 4
Saturday, August 15, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 3
Monday, August 17, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 3
Tuesday, August 18, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 3
Tuesday, August 18, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 1
Wednesday, August 19, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 4
Thursday, August 20, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 4
Thursday, August 20, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 2
Saturday, August 22, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 4
Saturday, August 22, 2020​	9:00:00 AM​	4:00:00 PM​	Class 8	Session 1
Monday, August 24, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 5
Tuesday, August 25, 2020​	6:00:00 PM​	8:00:00 PM​	Class 8	Session 2
Tuesday, August 25, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 3
Wednesday, August 26, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 6
Thursday, August 27, 2020​	6:00:00 PM​	8:00:00 PM​	Class 8	Session 3
Thursday, August 27, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 4
Saturday, August 29, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 5
Saturday, August 29, 2020​	9:00:00 AM​	4:00:00 PM​	Class 9	Session 1

At first, I was just going to highlight every time date changes... however, I think a better idea is just to build a vertical calendar...
This is how far I've gotten: (with some help from folks here!)

Helper1	Helper2	# Blank Lines to Add	Date	Start Time	End Time	Class	Session
20​	FALSE​	1​	Saturday, August 1, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 1
21​	FALSE​	2​	Tuesday, August 4, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 1
22​	FALSE​	0​	Wednesday, August 5, 2020​	1:00:00 PM​	3:00:00 PM​	Class 3	Session 1
22​	TRUE​	0​	Wednesday, August 5, 2020​	6:30:00 PM​	8:00:00 PM​	Class 4	Session 1
23​	FALSE​	0​	Thursday, August 6, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 2
24​	FALSE​	1​	Saturday, August 8, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 2
25​	FALSE​	1​	Monday, August 10, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 1
26​	FALSE​	0​	Tuesday, August 11, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 1
26​	TRUE​	0​	Tuesday, August 11, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 3
27​	FALSE​	0​	Wednesday, August 12, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 2
28​	FALSE​	0​	Thursday, August 13, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 2
28​	TRUE​	0​	Thursday, August 13, 2020​	6:30:00 PM​	9:30:00 PM​	Class 2	Session 4
29​	FALSE​	1​	Saturday, August 15, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 3
30​	FALSE​	1​	Monday, August 17, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 3
31​	FALSE​	0​	Tuesday, August 18, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 3
31​	TRUE​	0​	Tuesday, August 18, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 1
32​	FALSE​	0​	Wednesday, August 19, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 4
33​	FALSE​	0​	Thursday, August 20, 2020​	6:00:00 PM​	8:00:00 PM​	Class 6	Session 4
33​	TRUE​	0​	Thursday, August 20, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 2
34​	FALSE​	1​	Saturday, August 22, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 4
34​	TRUE​	0​	Saturday, August 22, 2020​	9:00:00 AM​	4:00:00 PM​	Class 8	Session 1
35​	FALSE​	1​	Monday, August 24, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 5
36​	FALSE​	0​	Tuesday, August 25, 2020​	6:00:00 PM​	8:00:00 PM​	Class 8	Session 2
36​	TRUE​	0​	Tuesday, August 25, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 3
37​	FALSE​	0​	Wednesday, August 26, 2020​	6:00:00 PM​	8:00:00 PM​	Class 5	Session 6
38​	FALSE​	0​	Thursday, August 27, 2020​	6:00:00 PM​	8:00:00 PM​	Class 8	Session 3
38​	TRUE​	0​	Thursday, August 27, 2020​	6:30:00 PM​	9:30:00 PM​	Class 7	Session 4
39​	FALSE​	1​	Saturday, August 29, 2020​	9:00:00 AM​	1:00:00 PM​	Class 1	Session 5
39​	TRUE​	0​	Saturday, August 29, 2020​	9:00:00 AM​	4:00:00 PM​	Class 9	Session 1

I don't think I need Help Column 1 anymore... I was using it to highlight every time a date changed (i.e. filtering to odd numbers and highlighting);
Anyway, I now have FINALLY figured out how to insert the correct number of blank lines between dates; i.e. if a class if offered on the 1st and the next day a class if offered is on the 4th, then I need to add two blank lined to serve as placeholders for August 2 and August 3.

My problem is, how do I do a macro to populate the date field for the new blank cells? I want to physically have August 2 and August 3... but again, I don't know the way to program this. Looks simple, seems simple, but... so any help would be greatly appreciated.

As an added bonus, my idea is to created a formatted calendar and more the classes to the calendar (I know, I could do this simpler by creating a file for a calendar program to read, but there are other things/reasons to create a custom calendar.

For example, I want to create Aug 1 in cell A1 (This assume Aug. 1 falls on Sunday... don't think it does, but as an example). then I'll leave A2, A3, A4, A5, A6 blank to allow me to put up to 5 sessions that may be scheduled on Aug. 1. Then same thing for Aug 2. Put it in cell B1, leave B2 to B6 blank for up to 5 sessions for Aug. 2... etc. etc.

(To me this also sounds kind of simple, but I'm sure there will be problems... was going to try and use VLookup... although I'm not sure how I figure out which of the 5 blanks on a date I use... i.e. I have three sessions on Aug 1... how do I get the first session in the first blank for Aug 1... then test to see if next row is an Aug 1 class... if so, make sure that session goes into black two for Aug. 1, etc. Hope this isn't too confusing....

Again, certainly would appreciate some excel whiz helping me out! . Thanks in advance!

 

[abuchanan]

Hey let me check something before you go... looks a little weird at bottom!

 

[abuchanan]

Nope... works great. Was just another case of lose nut behind the keyboard!

Thanks again for the help!!!

 

[alansidman]

Thanks for the feedback

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[abuchanan]

You guys are great!

 

[Fluff]

 

[abuchanan]

I have a question related to this already. Should I open a new thread?
I created a calendar using pivot tables, which works fine. However, I need to put the course titles in on the calendar as value and have it to print the text. From what I see on the internet, this is a somewhat easy fix using power pivot and DAX... however, I am on a MAC so I don't think I have this option available.

Also I can't really use conditional formatting since there are so many classes. Is there any other suggestions? Is this a new thread? Thanks

 

[Fluff]

As it's a totally different question, it will need a new thread. Thanks.