How to fill in dates based on previous dates

A

abuchanan

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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
So that we don't have to re-type your data, would you reload it to this site using XL2BB. Look at the link in my signature for help on loading.
 
Upvote 0
Alan... is this what you need?
This is the original file:

OA_Schedule_REV4 (1).xlsm
ABCDE
1DateStart TimeEnd TimeClassSession
2Saturday, August 1, 20209:00:00 AM1:00:00 PMClass 1Session 1
3Tuesday, August 4, 20206:30:00 PM9:30:00 PMClass 2Session 1
4Wednesday, August 5, 20201:00:00 PM3:00:00 PMClass 3Session 1
5Wednesday, August 5, 20206:30:00 PM8:00:00 PMClass 4Session 1
6Thursday, August 6, 20206:30:00 PM9:30:00 PMClass 2Session 2
7Saturday, August 8, 20209:00:00 AM1:00:00 PMClass 1Session 2
8Monday, August 10, 20206:00:00 PM8:00:00 PMClass 5Session 1
9Tuesday, August 11, 20206:00:00 PM8:00:00 PMClass 6Session 1
10Tuesday, August 11, 20206:30:00 PM9:30:00 PMClass 2Session 3
11Wednesday, August 12, 20206:00:00 PM8:00:00 PMClass 5Session 2
12Thursday, August 13, 20206:00:00 PM8:00:00 PMClass 6Session 2
13Thursday, August 13, 20206:30:00 PM9:30:00 PMClass 2Session 4
14Saturday, August 15, 20209:00:00 AM1:00:00 PMClass 1Session 3
15Monday, August 17, 20206:00:00 PM8:00:00 PMClass 5Session 3
16Tuesday, August 18, 20206:00:00 PM8:00:00 PMClass 6Session 3
17Tuesday, August 18, 20206:30:00 PM9:30:00 PMClass 7Session 1
18Wednesday, August 19, 20206:00:00 PM8:00:00 PMClass 5Session 4
19Thursday, August 20, 20206:00:00 PM8:00:00 PMClass 6Session 4
20Thursday, August 20, 20206:30:00 PM9:30:00 PMClass 7Session 2
21Saturday, August 22, 20209:00:00 AM1:00:00 PMClass 1Session 4
22Saturday, August 22, 20209:00:00 AM4:00:00 PMClass 8Session 1
23Monday, August 24, 20206:00:00 PM8:00:00 PMClass 5Session 5
24Tuesday, August 25, 20206:00:00 PM8:00:00 PMClass 8Session 2
25Tuesday, August 25, 20206:30:00 PM9:30:00 PMClass 7Session 3
26Wednesday, August 26, 20206:00:00 PM8:00:00 PMClass 5Session 6
27Thursday, August 27, 20206:00:00 PM8:00:00 PMClass 8Session 3
28Thursday, August 27, 20206:30:00 PM9:30:00 PMClass 7Session 4
29Saturday, August 29, 20209:00:00 AM1:00:00 PMClass 1Session 5
30Saturday, August 29, 20209:00:00 AM4:00:00 PMClass 9Session 1
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:D32Expression=ISODD(#REF!)textNO




This is how far I got:

OA_Schedule_REV4 (1).xlsm
ABCDEFGH
1Helper1Helper2# Blank Lines to AddDateStart TimeEnd TimeClassSession
220FALSE1Saturday, August 1, 20209:00:00 AM1:00:00 PMClass 1Session 1
3
4
521FALSE2Tuesday, August 4, 20206:30:00 PM9:30:00 PMClass 2Session 1
622FALSE0Wednesday, August 5, 20201:00:00 PM3:00:00 PMClass 3Session 1
722TRUE0Wednesday, August 5, 20206:30:00 PM8:00:00 PMClass 4Session 1
823FALSE0Thursday, August 6, 20206:30:00 PM9:30:00 PMClass 2Session 2
9
1024FALSE1Saturday, August 8, 20209:00:00 AM1:00:00 PMClass 1Session 2
11
1225FALSE1Monday, August 10, 20206:00:00 PM8:00:00 PMClass 5Session 1
1326FALSE0Tuesday, August 11, 20206:00:00 PM8:00:00 PMClass 6Session 1
1426TRUE0Tuesday, August 11, 20206:30:00 PM9:30:00 PMClass 2Session 3
1527FALSE0Wednesday, August 12, 20206:00:00 PM8:00:00 PMClass 5Session 2
1628FALSE0Thursday, August 13, 20206:00:00 PM8:00:00 PMClass 6Session 2
1728TRUE0Thursday, August 13, 20206:30:00 PM9:30:00 PMClass 2Session 4
18
1929FALSE1Saturday, August 15, 20209:00:00 AM1:00:00 PMClass 1Session 3
20
2130FALSE1Monday, August 17, 20206:00:00 PM8:00:00 PMClass 5Session 3
2231FALSE0Tuesday, August 18, 20206:00:00 PM8:00:00 PMClass 6Session 3
2331TRUE0Tuesday, August 18, 20206:30:00 PM9:30:00 PMClass 7Session 1
2432FALSE0Wednesday, August 19, 20206:00:00 PM8:00:00 PMClass 5Session 4
2533FALSE0Thursday, August 20, 20206:00:00 PM8:00:00 PMClass 6Session 4
2633TRUE0Thursday, August 20, 20206:30:00 PM9:30:00 PMClass 7Session 2
27
2834FALSE1Saturday, August 22, 20209:00:00 AM1:00:00 PMClass 1Session 4
2934TRUE0Saturday, August 22, 20209:00:00 AM4:00:00 PMClass 8Session 1
30
3135FALSE1Monday, August 24, 20206:00:00 PM8:00:00 PMClass 5Session 5
3236FALSE0Tuesday, August 25, 20206:00:00 PM8:00:00 PMClass 8Session 2
3336TRUE0Tuesday, August 25, 20206:30:00 PM9:30:00 PMClass 7Session 3
3437FALSE0Wednesday, August 26, 20206:00:00 PM8:00:00 PMClass 5Session 6
3538FALSE0Thursday, August 27, 20206:00:00 PM8:00:00 PMClass 8Session 3
3638TRUE0Thursday, August 27, 20206:30:00 PM9:30:00 PMClass 7Session 4
37
3839FALSE1Saturday, August 29, 20209:00:00 AM1:00:00 PMClass 1Session 5
3939TRUE0Saturday, August 29, 20209:00:00 AM4:00:00 PMClass 9Session 1
Sheet1 (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A39:G39Expression=ISODD($B39)textNO
A38:G38Expression=ISODD($B38)textNO
A34:G34Expression=ISODD($B34)textNO
A31:G31Expression=ISODD($B31)textNO
A26:G26Expression=ISODD($B26)textNO
A25:G25Expression=ISODD($B25)textNO
A23:G23Expression=ISODD($B23)textNO
A22:G22Expression=ISODD($B22)textNO
A19:G19Expression=ISODD($B19)textNO
A15:G15Expression=ISODD($B15)textNO
A12:G12Expression=ISODD($B12)textNO
A8:G8Expression=ISODD($B8)textNO
A2:G7,A9:G11,A13:G14,A16:G18,A20:G21,A24:G24,A27:G30,A32:G33,A35:G37,A40:G41Expression=ISODD($B2)textNO
 
Upvote 0
Here is a VBA solution for you

Rich (BB code): 
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
 
Upvote 0
I'm not sure we 100% on the same page. I want to make sure I have at least one row for every date; every date need to show in the final file.

I think the VBA you supplied cuts it back so that there is one blank row between session dates.

What I have to start with...

Jan 1 Class 1 Session 1
Jan 2 Class 2 Session 1
Jan 5 Class 1 Session 2
Jan 7 Class 3 Session 1
Jan 12 Class 2 Session 2

What I want:

Jan 1 Class 1 Session 1
Jan 2 Class 2 Session 1
Jan 3
Jan 4
Jan 5 Class 1 Session 2
Jan 6
Jan 7 Class 3 Session 1
Jan 8
Jan 9
Jan 10
Jan 11
Jan 12 Class 2 Session 2

There still will be a line for days with no sessions (i.e. Jan 3,4,6,8,9,10,11) -- the only info in those lines will be the dates --

I have figured out how to insert the correct number of blank line. I just can't automatically fill in the dates (unless I go to each line and pull the dates down... that is what I don't want to have to do each month!). Everything I try either doesn't work or I end up missing a date (usually just doesn't work.)

I'm still trying to remember how to put stuff in the forum and probably screwed up. I had some proprietary data in column A, so I just cut out the entire column. Instead, I should have dummied that data out and left the column in (I was just going to change your columns by one letter (i.e. change A to B, etc.) to what is the real columns in my data; but in hindsight that was pretty stupid... I'm not sure if I can always tell exactly what needs to be changed.

Also I realize being pretty stupid in how I added the helper column. I should have put that at the end of the row so that the same data remains in all columns from start to finish (instead of moving everything over every time I add in a help column in the middle of the data! Not smart.
Sorry for being such a pain!
 
Upvote 0
Is this what you want
Before
+Fluff New.xlsm
ABCDEF
1DateStart TimeEnd TimeClassSession
201/08/20209:0013:00Class 1Session 1
304/08/202018:3021:30Class 2Session 1
405/08/202013:0015:00Class 3Session 1
505/08/202018:3020:00Class 4Session 1
606/08/202018:3021:30Class 2Session 2
708/08/20209:0013:00Class 1Session 2
810/08/202018:0020:00Class 5Session 1
911/08/202018:0020:00Class 6Session 1
1011/08/202018:3021:30Class 2Session 3
1112/08/202018:0020:00Class 5Session 2
Data


After
+Fluff New.xlsm
ABCDEF
1DateStart TimeEnd TimeClassSession
201/08/20209:0013:00Class 1Session 1
302/08/2020
403/08/2020
504/08/202018:3021:30Class 2Session 1
605/08/202013:0015:00Class 3Session 1
705/08/202018:3020:00Class 4Session 1
806/08/202018:3021:30Class 2Session 2
907/08/2020
1008/08/20209:0013:00Class 1Session 2
1109/08/2020
1210/08/202018:0020:00Class 5Session 1
1311/08/202018:0020:00Class 6Session 1
1411/08/202018:3021:30Class 2Session 3
1512/08/202018:0020:00Class 5Session 2
Data
 
Upvote 0
In that case how about
VBA Code: 
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
 
Upvote 0
I guess I'm doing something wrong. This looks like it just adds a blank line... :)

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.

Just to stay on same page, I have already added the correct number of blank lines (I may not have done efficiently, but I have done that.). So my assumption is to run the above on the file with the correct lines already added. But when I do that, it looks like it just adds one more blank line to the blank lines I already have. None of the dates populate at all... just blank lines.

I can't believe this would have anything to do with it, but my date column is formatted as:
Saturday, August 1, 2020
Sunday, August 2, 2020

and then of course there are 3 blank lines, and then I have
Thursday, August 6, 2020...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
68
cooleomter
C
M
  • Solved
sumifs using date and time values
Replies
9
Views
327
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Hi. I'm trying to fill 1 for the time between arrival and departure.
Replies
1
Views
164
Asbestos_Jen
Asbestos_Jen
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
450
Peter_SSs
Peter_SSs
J
  • Question Question
XLOOKUP, Finding a Value based on Time and Date
Replies
1
Views
312
SunnyAlv
SunnyAlv

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

Share this page

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
Back
Top