Populating a weekly schedule from a date list

[Jezarooo]

Hi there,

I'm working on a project that involves a worksheet table containing a long list of dates and a range of extra data. I would like to use this table's data and convert it in an easier to read weekly schedule format.

Below is a quick sample layout and structure:

Date List Worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Reference[/TD]
[/TR]
[TR]
[TD]01/08/2016[/TD]
[TD]10:00AM[/TD]
[TD]11:00AM[/TD]
[TD]Lesson 1[/TD]
[/TR]
[TR]
[TD]01/08/2016[/TD]
[TD]2:00PM[/TD]
[TD]4:00PM[/TD]
[TD]Meeting[/TD]
[/TR]
[TR]
[TD]02/08/2016[/TD]
[TD]2:00PM[/TD]
[TD]3:00PM[/TD]
[TD]Lesson 2[/TD]
[/TR]
[TR]
[TD]05/08/2016[/TD]
[TD]12:00PM[/TD]
[TD]2:00PM[/TD]
[TD]Lesson 3[/TD]
[/TR]
</tbody>[/TABLE]

Calendar Worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]MONDAY[/TD]
[TD]TUESDAY[/TD]
[TD]WEDNESDAY[/TD]
[TD]THURSDAY[/TD]
[TD]FRIDAY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/08/2016[/TD]
[TD]02/08/2016[/TD]
[TD]03/08/2016[/TD]
[TD]04/08/2016[/TD]
[TD]05/08/2016[/TD]
[/TR]
[TR]
[TD]9AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10AM[/TD]
[TD]Lesson 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lesson 3[/TD]
[/TR]
[TR]
[TD]1PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lesson 3[/TD]
[/TR]
[TR]
[TD]2PM[/TD]
[TD]Meeting[/TD]
[TD]Lesson 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3PM[/TD]
[TD]Meeting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So far I've managed to create the Calendar worksheet in a way that allows meet to select the weekly date to display, following the guide here: Populate cells dynamically in a weekly schedule in excel | Get Digital Help - Microsoft Excel resource. However, I am having problems working out how to get Calendar worksheet to reference my Date List worksheet and the pull the content from the appropriate reference cell.

Also, once I've cracked that one. I need to be able to display more than one text string from the reference cell in each Calendar date cell (for example there might be Lesson 1, Lesson 2, and a Meeting all in the same time slot). As I understand it this may require some VBA (which is way beyond me), but if there's a way of achieving this with just functions please let me know.

Thanks in advance for your help!

J

 

[Jezarooo]

FYI

Here's an Excel sample of my description above: https://anglokom.bitrix24.com/~t5OUH

Again, thanks in advance!

 

[Jeffrey Mahoney]

Do you need to have all the possible times listed in the calendar? Meaning, every hour from am to pm? Or just the times listed?

 

[Jeffrey Mahoney]

I know this is going to be a lot to do, but I think you'll like it. It may need some tweaking depending on your tastes.

The code below takes the Schedule Table which is just the list of dates and times and turns them into a calendar format you wanted.

You need to create some named ranges for this to work:

Cell A1 = Schedule_hdr (Sheet level scope)

Dynamic named range = Schedule_tbl
refers to: =OFFSET(Sheet21!Schedule_hdr,1,0,COUNTA(OFFSET(Sheet21!Schedule_hdr,1,0,1000000,1)),4)
Replace Sheet21 with the name of your sheet in this formula

Cell F2 = Calendar_hdr (Sheet level scope)

Sub CreateCalendar()  Dim Schedule As Range
  Dim SDates As Range
  Dim Cel As Range
  Dim TL As Range
  Dim CalHdr As Range
  Dim CalTimes As Range
  Dim LastDate As Date
  Dim CurDate As Date
  Dim Col As Long
  Dim Row As Long
  Dim CC As Range
  Dim CurTime As Single
  Dim TimeRow As Long
  Dim TimeLen As String
  Dim EndTime As Single
  Dim RefTxt As String
  Dim TimeDif As Single
  Dim Key2 As Range
  Dim FirstCalTime As Single
  Dim A As String
  
  Set Schedule = Range("Schedule_tbl")
  Set TL = Schedule.Resize(1, 1)
  Set SDates = Range(TL, TL.Offset(Schedule.Rows.Count - 1, 0))
  Set Key2 = Range(TL.Offset(0, 1), TL.End(xlDown).Offset(0, 1))
  
  Set CalHdr = Range("Calendar_hdr")
  Set CalTimes = Range(CalHdr.Offset(1, 0), CalHdr.End(xlDown))
  TimeRow = CalHdr.Row
  FirstCalTime = CalHdr.Offset(1, 0).Value
  
  Range(CalHdr.Offset(-1, 1), CalHdr.End(xlToRight).Offset(CalTimes.Rows.Count, 0)).ClearContents
  
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=SDates, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  ActiveSheet.Sort.SortFields.Add Key:=Key2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveSheet.Sort
    .SetRange Schedule
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  
  LastDate = TL.Value
  CalHdr.Offset(0, 1).Value = LastDate
  Col = CalHdr.Offset(0, 1).Column
  A = CalHdr.End(xlToRight).Address(0, 0)
  CalHdr.End(xlToRight).Offset(-1, 0).Formula = "=" & A
  CalHdr.End(xlToRight).Offset(-1, 0).NumberFormat = "dddd"
  For Each Cel In SDates
    CurDate = Cel.Value
    If CurDate > LastDate Then
      Col = CalHdr.End(xlToRight).Offset(0, 1).Column
      CalHdr.End(xlToRight).Offset(0, 1).Value = CurDate
      A = CalHdr.End(xlToRight).Address(0, 0)
      CalHdr.End(xlToRight).Offset(-1, 0).Formula = "=" & A
      CalHdr.End(xlToRight).Offset(-1, 0).NumberFormat = "dddd"
      LastDate = CurDate
      
    End If
    CurTime = Cel.Offset(0, 1).Value
    EndTime = Cel.Offset(0, 2).Value
    TimeDif = EndTime - CurTime
    TimeDif = (TimeDif * (24 * 60)) / 60
    If Abs(TimeDif - Int(TimeDif)) < 0.0001 Then
      TimeLen = Format(TimeDif, "0hr")
    ElseIf Abs(TimeDif - Int(TimeDif)) < 0.0001 Then
      TimeLen = Format(TimeDif, "0hrs")
    Else
      TimeLen = Format(TimeDif, "0.0hrs")
    End If
    Row = (((CurTime - FirstCalTime) * (24 * 60)) / 60) * 2 + TimeRow + 1
    'Row = Application.Match(CurTime, CalTimes, 0) + TimeRow
    Set CC = Cells(Row, Col)
    RefTxt = Cel.Offset(0, 3).Value
    If Len(CC.Value) > 0 Then
      CC.Value = CC.Value & "," & RefTxt & "-" & TimeLen
    Else
      CC.Value = RefTxt & "-" & TimeLen
    End If
  Next Cel
  
      
  
  
End Sub

Excel 2010

	A	B	C	D	E	F	G	H	I	J
Lesson 1
Meeting	Lesson 4-2hr
Lesson 2
Lesson 3
Lesson 4
Lesson 1-1hr
Meeting-2hr	Lesson 2-1hr
Lesson 3--3hr

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Reference[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Friday[/TD]
[TD="align: right"]Monday[/TD]
[TD="align: right"]Sunday[/TD]
[TD="align: right"]Tuesday[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/8/2016[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Time[/TD]
[TD="align: right"]1/8/2016[/TD]
[TD="align: right"]2/8/2016[/TD]
[TD="align: right"]5/8/2016[/TD]
[TD="align: right"]5/10/2016[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/8/2016[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/8/2016[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7:30 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5/8/2016[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5/10/2016[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8:30 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9:30 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00 AM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:30 AM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6:30 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G1[/TH]
[TD="align: left"]=G2[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H1[/TH]
[TD="align: left"]=H2[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I1[/TH]
[TD="align: left"]=I2[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J1[/TH]
[TD="align: left"]=J2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeffrey Mahoney]

Just a couple little notes. For some reason when I copied your tables, some of the dates and times came over as text instead of date and time values. When I created the Calendar table I used the Drag Fill option to create the daily times. Those times ended up not matching the times entered into the schedule, so I had to enter all the times manually. The macro depends on the times from the schedule matching to times in the Calendar.

If you need to have a calendar with an increment of time different than what I provided, we would have to change the macro.

Jeff

 

[Jezarooo]

Just a couple little notes. For some reason when I copied your tables, some of the dates and times came over as text instead of date and time values. When I created the Calendar table I used the Drag Fill option to create the daily times. Those times ended up not matching the times entered into the schedule, so I had to enter all the times manually. The macro depends on the times from the schedule matching to times in the Calendar.

If you need to have a calendar with an increment of time different than what I provided, we would have to change the macro.

Jeff

Hi Jeff,

Thanks very much for helping! Good spot with the times, I will indeed need it to be in 30 minute blocks, but the times I listed should be fine (we're a school, so very unlikely to be tutoring outside of those hours).

However, I'm afraid I'm struggling to get it to work. I'm a little unfamiliar with Macros and I'm not sure I'm placing the code in the correct place. Please see here to see where I've got to so far: https://anglokom.bitrix24.com/~t5OUH. If possible would you mind highlighting where I've gone wrong so I can learn for next time?

Also with regards to the structure of the calendar, will this will be OK with the schedule and the calendar on separate sheets? The actual schedule sheet has significantly more columns so needs the calendar to be presented elsewhere.

Thanks!

J

 

[Jeffrey Mahoney]

It's late, so I'm not going into detail until tomorrow. Macro workbooks need to be saved as .xlsm. The code needs to go in a standard module. Alt-F11, right click on the VBA project for your workbook on the left, choose insert - module. Paste the code into the main screen on the right. You'll probably want to create a button on the Calendar sheet. Yes, you can make this work with seperate sheets for the list and Calendar.

Jeff

 

[Jeffrey Mahoney]

In post #4 I described how you needed to create some named ranges. Please add those named ranges if you want the macro to work.

 

[Jezarooo]

In post #4 I described how you needed to create some named ranges. Please add those named ranges if you want the macro to work.

Hi Jeff,

Thanks for your help. I was unfamiliar with named ranges but I get the gyst now

Thanks!

Jez