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!
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,183
Office Version
  1. 365
Platform
  1. Windows
If you ran the macro I provided on the sample data you provided then the following is the result

Book12.xlsm
ABCDE
1DateStart TimeEnd TimeClassSession
28/1/20209:00:00 AM1:00:00 PMClass 1Session 1
38/2/2020
48/3/2020
58/4/20206:30:00 PM9:30:00 PMClass 2Session 1
68/5/20201:00:00 PM3:00:00 PMClass 3Session 1
78/5/20206:30:00 PM8:00:00 PMClass 4Session 1
88/6/20206:30:00 PM9:30:00 PMClass 2Session 2
98/7/2020
108/8/20209:00:00 AM1:00:00 PMClass 1Session 2
118/9/2020
128/10/20206:00:00 PM8:00:00 PMClass 5Session 1
138/11/20206:00:00 PM8:00:00 PMClass 6Session 1
148/11/20206:30:00 PM9:30:00 PMClass 2Session 3
158/12/20206:00:00 PM8:00:00 PMClass 5Session 2
168/13/20206:00:00 PM8:00:00 PMClass 6Session 2
178/13/20206:30:00 PM9:30:00 PMClass 2Session 4
188/14/2020
198/15/20209:00:00 AM1:00:00 PMClass 1Session 3
208/16/2020
218/17/20206:00:00 PM8:00:00 PMClass 5Session 3
228/18/20206:00:00 PM8:00:00 PMClass 6Session 3
238/18/20206:30:00 PM9:30:00 PMClass 7Session 1
248/19/20206:00:00 PM8:00:00 PMClass 5Session 4
258/20/20206:00:00 PM8:00:00 PMClass 6Session 4
268/20/20206:30:00 PM9:30:00 PMClass 7Session 2
278/21/2020
288/22/20209:00:00 AM1:00:00 PMClass 1Session 4
298/22/20209:00:00 AM4:00:00 PMClass 8Session 1
308/23/2020
318/24/20206:00:00 PM8:00:00 PMClass 5Session 5
328/25/20206:00:00 PM8:00:00 PMClass 8Session 2
338/25/20206:30:00 PM9:30:00 PMClass 7Session 3
348/26/20206:00:00 PM8:00:00 PMClass 5Session 6
358/27/20206:00:00 PM8:00:00 PMClass 8Session 3
368/27/20206:30:00 PM9:30:00 PMClass 7Session 4
378/28/2020
388/29/20209:00:00 AM1:00:00 PMClass 1Session 5
398/29/20209:00:00 AM4:00:00 PMClass 9Session 1
Sheet1
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
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

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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you please answer my question from post#12?
 

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32
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 IDMeeting DateStart TimeEnd TimeTitleInstructor
592​
Wednesday, May 13, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
Wednesday, May 27, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
Wednesday, June 10, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
Wednesday, June 24, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
Wednesday, July 8, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
Wednesday, July 22, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
591​
Wednesday, May 13, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
591​
Wednesday, May 27, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
591​
Wednesday, June 10, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
591​
Wednesday, June 24, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
591​
Wednesday, July 8, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
591​
Wednesday, July 22, 2020​
4:00 PM​
8:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
615​
Tuesday, July 7, 2020​
6:30 PM​
9:30 PM​
Human Resource Management IntroductionLinda 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 IDHelper 1Helper 2Lines to add aboveMeeting DateStart TimeEnd TimeTitleInstructor
592​
1​
Wednesday, May 13, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
1​
TRUE​
0​
Wednesday, May 13, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
592​
2​
FALSE​
13​
Wednesday, May 27, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
2​
TRUE​
0​
Wednesday, May 27, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
592​
3​
FALSE​
13​
Wednesday, June 10, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
592​
3​
TRUE​
0​
Wednesday, June 10, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
591​
4​
FALSE​
13​
Wednesday, June 24, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
591​
4​
TRUE​
0​
Wednesday, June 24, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
591​
5​
FALSE​
12​
Tuesday, July 7, 2020​
6:30:00 PM​
9:30:00 PM​
Human Resource Management IntroductionLinda Burgess
591​
6​
FALSE​
0​
Wednesday, July 8, 2020​
1:00:00 PM​
3:00:00 PM​
Continuous Improvement FundamentalsShane Wen
591​
6​
TRUE​
0​
Wednesday, July 8, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Black Belt CertificationShane Wen
591​
6​
TRUE​
0​
Wednesday, July 8, 2020​
4:00:00 PM​
8:00:00 PM​
Lean Six Sigma Green Belt CertificationJohn Riggs
615​
7​
FALSE​
0​
Thursday, July 9, 2020​
6:30:00 PM​
9:30:00 PM​
Human Resource Management IntroductionLinda Burgess
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

abuchanan

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
You need to run it on your original file, not the one you modified.
 

abuchanan

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

Watch MrExcel Video

Forum statistics

Threads
1,129,269
Messages
5,635,190
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