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!

 

[alansidman]

If you ran the macro I provided on the sample data you provided then the following is the result

Book12.xlsm
	A	B	C	D	E
1	Date	Start Time	End Time	Class	Session
2	8/1/2020	9:00:00 AM	1:00:00 PM	Class 1	Session 1
3	8/2/2020
4	8/3/2020
5	8/4/2020	6:30:00 PM	9:30:00 PM	Class 2	Session 1
6	8/5/2020	1:00:00 PM	3:00:00 PM	Class 3	Session 1
7	8/5/2020	6:30:00 PM	8:00:00 PM	Class 4	Session 1
8	8/6/2020	6:30:00 PM	9:30:00 PM	Class 2	Session 2
9	8/7/2020
10	8/8/2020	9:00:00 AM	1:00:00 PM	Class 1	Session 2
11	8/9/2020
12	8/10/2020	6:00:00 PM	8:00:00 PM	Class 5	Session 1
13	8/11/2020	6:00:00 PM	8:00:00 PM	Class 6	Session 1
14	8/11/2020	6:30:00 PM	9:30:00 PM	Class 2	Session 3
15	8/12/2020	6:00:00 PM	8:00:00 PM	Class 5	Session 2
16	8/13/2020	6:00:00 PM	8:00:00 PM	Class 6	Session 2
17	8/13/2020	6:30:00 PM	9:30:00 PM	Class 2	Session 4
18	8/14/2020
19	8/15/2020	9:00:00 AM	1:00:00 PM	Class 1	Session 3
20	8/16/2020
21	8/17/2020	6:00:00 PM	8:00:00 PM	Class 5	Session 3
22	8/18/2020	6:00:00 PM	8:00:00 PM	Class 6	Session 3
23	8/18/2020	6:30:00 PM	9:30:00 PM	Class 7	Session 1
24	8/19/2020	6:00:00 PM	8:00:00 PM	Class 5	Session 4
25	8/20/2020	6:00:00 PM	8:00:00 PM	Class 6	Session 4
26	8/20/2020	6:30:00 PM	9:30:00 PM	Class 7	Session 2
27	8/21/2020
28	8/22/2020	9:00:00 AM	1:00:00 PM	Class 1	Session 4
29	8/22/2020	9:00:00 AM	4:00:00 PM	Class 8	Session 1
30	8/23/2020
31	8/24/2020	6:00:00 PM	8:00:00 PM	Class 5	Session 5
32	8/25/2020	6:00:00 PM	8:00:00 PM	Class 8	Session 2
33	8/25/2020	6:30:00 PM	9:30:00 PM	Class 7	Session 3
34	8/26/2020	6:00:00 PM	8:00:00 PM	Class 5	Session 6
35	8/27/2020	6:00:00 PM	8:00:00 PM	Class 8	Session 3
36	8/27/2020	6:30:00 PM	9:30:00 PM	Class 7	Session 4
37	8/28/2020
38	8/29/2020	9:00:00 AM	1:00:00 PM	Class 1	Session 5
39	8/29/2020	9:00:00 AM	4:00:00 PM	Class 9	Session 1
Sheet1

 

[Fluff]

I thought you may be starting from the very original file? (i.e. before I had added the blank lines), but tried on that and couldn't get to work.

I was, is your file laid out as the sample I posted in post#7?

 

[abuchanan]

Alan,

Thanks for the help. Unfortunately I still can't get this to work. My files are identical to what I posted. Maybe because I'm on a Mac? (doesn't make sense, but don't know another explanation.)

I was trying to capture what I have to start and then after I run your code, but now the Mr. Excel capture button just hangs up my excel and I can do nothing but force it to quit. Not sure what is going on. I think I just may be out of luck!

I tried to uninstall Mr. Excel (as you put in your post) and re install, and still nothing. I feel like this may be due to Mac also. In fact, some of the options that are listed to install are not in my version of excel. I have Version 16.39 (20071300).

Thanks again for the help. I may just give up.


This is code I put in...
Option Explicit

Sub AddDates()
Dim lr As Long, i As Long
i = 3
lr = Range("A" & Rows.Count).End(xlUp).Row
Do Until IsEmpty(Cells(i, 1))
If Range("A" & i - 1) <> Range("A" & i) Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i - 1).Value2 + 1
End If
i = i + 1
Loop

lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
If Range("B" & i) = "" And Range("A" & i) = Range("A" & i + 1) Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub

 

[abuchanan]

Fluff;

Tired your code and getting similar results. All these do is either add or take out blank lines. I guess I have no idea what I am doing. Thanks for your help anyways. Again, would love to show you guys what I have before and after I run, but I cannot get the XL2BB to work. It completely hangs up my excel and I have to force quit.

 

[Fluff]

Can you please answer my question from post#12?

 

[abuchanan]

SHORT ANSWER IS YES... but not the exact file... below is the EXACT file except I changed the name of instructor
There is more specific info below...
Again, apologize I can't attach files correctly. I know this is frustrating (at least it is for me!!).

I receive a file from our IT group that is as follows (once I import into excel):

Course ID	Meeting Date	Start Time	End Time	Title	Instructor
592​	Wednesday, May 13, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	Wednesday, May 27, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	Wednesday, June 10, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	Wednesday, June 24, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	Wednesday, July 8, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	Wednesday, July 22, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
591​	Wednesday, May 13, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
591​	Wednesday, May 27, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
591​	Wednesday, June 10, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
591​	Wednesday, June 24, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
591​	Wednesday, July 8, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
591​	Wednesday, July 22, 2020​	4:00 PM​	8:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
615​	Tuesday, July 7, 2020​	6:30 PM​	9:30 PM​	Human Resource Management Introduction	Linda Burgess

This file goes on for long ways...

After I run some code, I get the above file as follows:
My HELPER 1 Column was initially because all I was going to do was to highlight every time the date changed. Helper 1 was put in prior to me decided to add the blank lines. So the two classes on Wed 13th would be no highlight, then the date changes to Wed. 27... these two would be red, etc. etc.

After though, it was much easier on everyone if I just made this into a calendar. So I wanted to add at least one line for each date; so I had to count the number of days (lines needed) between the dates. From Wed 13th to Wed 27, I needed 13 blank lines... so you will see the number 13 in the "Lines to Add Above" on the first Wednesday May 27 line. I had to add 13 blank lines BEFORE Wednesday May 27. (I've worked on this a while, so can't remember now why I had to put the counter in to add lines before instead of after.)

Course ID	Helper 1	Helper 2	Lines to add above	Meeting Date	Start Time	End Time	Title	Instructor
592​	1​			Wednesday, May 13, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	1​	TRUE​	0​	Wednesday, May 13, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
592​	2​	FALSE​	13​	Wednesday, May 27, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	2​	TRUE​	0​	Wednesday, May 27, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
592​	3​	FALSE​	13​	Wednesday, June 10, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
592​	3​	TRUE​	0​	Wednesday, June 10, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
591​	4​	FALSE​	13​	Wednesday, June 24, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
591​	4​	TRUE​	0​	Wednesday, June 24, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
591​	5​	FALSE​	12​	Tuesday, July 7, 2020​	6:30:00 PM​	9:30:00 PM​	Human Resource Management Introduction	Linda Burgess
591​	6​	FALSE​	0​	Wednesday, July 8, 2020​	1:00:00 PM​	3:00:00 PM​	Continuous Improvement Fundamentals	Shane Wen
591​	6​	TRUE​	0​	Wednesday, July 8, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Black Belt Certification	Shane Wen
591​	6​	TRUE​	0​	Wednesday, July 8, 2020​	4:00:00 PM​	8:00:00 PM​	Lean Six Sigma Green Belt Certification	John Riggs
615​	7​	FALSE​	0​	Thursday, July 9, 2020​	6:30:00 PM​	9:30:00 PM​	Human Resource Management Introduction	Linda Burgess

​

​

​

​

​

​

​

​

​

​

​

​

​

​

 

[Fluff]

If you run my code on your original file what happens?
Also are the dates real dates, or just text?

 

[abuchanan]

OK here's the code I put in:
Sub abuchanan()
Dim i As Long, x As Long

For i = Cells(Rows.Count, 2).End(xlUp).Row To 3 Step -1
x = Cells(i, 2) - Cells(i - 1, 2)
If x > 1 Then
Rows(i).Resize(x - 1).Insert
Cells(i - 1, 2).AutoFill Cells(i - 1, 2).Resize(x), xlFillDays
End If
Next i
End Sub

It adds one blank line to the first group of blanks, two blank lines to the second group of blanks, three blank lines to the third group of blanks, etc. etc. Dates are never filled in. It just continues to add one more blank line to each subsequent group.

 

[Fluff]

You need to run it on your original file, not the one you modified.

 

[abuchanan]

And there we go! I am so sorry I didn't try this (I actually thought I did, but I'm getting old!)

This works perfectly. Thank you so much. Again, I certainly apologize to both you and Alan!

I'm going to try to put it into a calendar, but I may give up before I get that done!