How to fill in dates based on previous dates

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32
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.

DateStart TimeEnd TimeClassSession
Saturday, August 1, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 1
Tuesday, August 4, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 1
Wednesday, August 5, 2020​
1:00:00 PM​
3:00:00 PM​
Class 3Session 1
Wednesday, August 5, 2020​
6:30:00 PM​
8:00:00 PM​
Class 4Session 1
Thursday, August 6, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 2
Saturday, August 8, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 2
Monday, August 10, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 1
Tuesday, August 11, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 1
Tuesday, August 11, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 3
Wednesday, August 12, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 2
Thursday, August 13, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 2
Thursday, August 13, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 4
Saturday, August 15, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 3
Monday, August 17, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 3
Tuesday, August 18, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 3
Tuesday, August 18, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 1
Wednesday, August 19, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 4
Thursday, August 20, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 4
Thursday, August 20, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 2
Saturday, August 22, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 4
Saturday, August 22, 2020​
9:00:00 AM​
4:00:00 PM​
Class 8Session 1
Monday, August 24, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 5
Tuesday, August 25, 2020​
6:00:00 PM​
8:00:00 PM​
Class 8Session 2
Tuesday, August 25, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 3
Wednesday, August 26, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 6
Thursday, August 27, 2020​
6:00:00 PM​
8:00:00 PM​
Class 8Session 3
Thursday, August 27, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 4
Saturday, August 29, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 5
Saturday, August 29, 2020​
9:00:00 AM​
4:00:00 PM​
Class 9Session 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!)

Helper1Helper2# Blank Lines to AddDateStart TimeEnd TimeClassSession
20​
FALSE​
1​
Saturday, August 1, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 1
21​
FALSE​
2​
Tuesday, August 4, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 1
22​
FALSE​
0​
Wednesday, August 5, 2020​
1:00:00 PM​
3:00:00 PM​
Class 3Session 1
22​
TRUE​
0​
Wednesday, August 5, 2020​
6:30:00 PM​
8:00:00 PM​
Class 4Session 1
23​
FALSE​
0​
Thursday, August 6, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 2
24​
FALSE​
1​
Saturday, August 8, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 2
25​
FALSE​
1​
Monday, August 10, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 1
26​
FALSE​
0​
Tuesday, August 11, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 1
26​
TRUE​
0​
Tuesday, August 11, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 3
27​
FALSE​
0​
Wednesday, August 12, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 2
28​
FALSE​
0​
Thursday, August 13, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 2
28​
TRUE​
0​
Thursday, August 13, 2020​
6:30:00 PM​
9:30:00 PM​
Class 2Session 4
29​
FALSE​
1​
Saturday, August 15, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 3
30​
FALSE​
1​
Monday, August 17, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 3
31​
FALSE​
0​
Tuesday, August 18, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 3
31​
TRUE​
0​
Tuesday, August 18, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 1
32​
FALSE​
0​
Wednesday, August 19, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 4
33​
FALSE​
0​
Thursday, August 20, 2020​
6:00:00 PM​
8:00:00 PM​
Class 6Session 4
33​
TRUE​
0​
Thursday, August 20, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 2
34​
FALSE​
1​
Saturday, August 22, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 4
34​
TRUE​
0​
Saturday, August 22, 2020​
9:00:00 AM​
4:00:00 PM​
Class 8Session 1
35​
FALSE​
1​
Monday, August 24, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 5
36​
FALSE​
0​
Tuesday, August 25, 2020​
6:00:00 PM​
8:00:00 PM​
Class 8Session 2
36​
TRUE​
0​
Tuesday, August 25, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 3
37​
FALSE​
0​
Wednesday, August 26, 2020​
6:00:00 PM​
8:00:00 PM​
Class 5Session 6
38​
FALSE​
0​
Thursday, August 27, 2020​
6:00:00 PM​
8:00:00 PM​
Class 8Session 3
38​
TRUE​
0​
Thursday, August 27, 2020​
6:30:00 PM​
9:30:00 PM​
Class 7Session 4
39​
FALSE​
1​
Saturday, August 29, 2020​
9:00:00 AM​
1:00:00 PM​
Class 1Session 5
39​
TRUE​
0​
Saturday, August 29, 2020​
9:00:00 AM​
4:00:00 PM​
Class 9Session 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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32
Nope... works great. Was just another case of lose nut behind the keyboard!

Thanks again for the help!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32

ADVERTISEMENT

You guys are great!
 

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
As it's a totally different question, it will need a new thread. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,270
Messages
5,635,207
Members
416,846
Latest member
ImGoing2needaFormula

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top