The Contract Calendar (aka "Effectivity Calendar")

My company has a report template I created that generates an executable agreement document based on Contract records in CRM. It does a lot of pretty formatting, but one thing I never had with it is a proper rendering of the contract's calendar (you know, that button on top of the contract).

This is mostly because the data for that calendar is saved in a single column called "effectivitycalendar" as a 168-character string of minus and plus signs. Not very helpful, by itself.

So, I initially took a shortcut, and output dates/times to the report based on the contract's service level. This worked fine, until recently I received a request to add another range of effective times to the contract (and by extension, the report). Without going into too much detail, it would have been ugly to rewrite portions of my contract customizations and report template. So, I banged out the following VBA code and placed it into the report:

Function ConvertEffectivityCalendar(ByVal effectivityString As String) As String 
  Dim dailyCodeArray() As String = New String() {Mid(effectivityString, 1, 24), Mid(effectivityString, 25, 24), Mid(effectivityString, 49, 24), Mid(effectivityString, 73, 24), Mid(effectivityString, 97, 24), Mid(effectivityString, 121, 24), Mid(effectivityString, 145, 24)}
  Dim dailyCode As String 
  Dim currentDay As Integer 
  Dim results As String 
  Dim startTime As Integer 
  Dim endTime As Integer 
  Dim nextStartTime As Integer 
  Dim nextEndTime As Integer 
  Dim lastStartTime As Integer 
  Dim lastEndTime As Integer 
  Dim today As String 
  
  For currentDay = 0 To 6 
    dailyCode = dailyCodeArray(currentDay)
    startTime = InStr(dailyCode, "+")
    endTime = InStrRev(dailyCode, "+")
    nextStartTime = 0 
    nextEndTime = 0 
    lastStartTime = 0 
    lastEndTime = 0 
    
    If currentDay < 6 Then 
      nextStartTime = InStr(dailyCodeArray(currentDay + 1), "+")
      nextEndTime = InStrRev(dailyCodeArray(currentDay + 1), "+")
    End If 
    
    If currentDay > 0 Then 
      lastStartTime = InStr(dailyCodeArray(currentDay - 1), "+")
      lastEndTime = InStrRev(dailyCodeArray(currentDay - 1), "+")
    End If 
    
    Select Case currentDay 
      Case 0 
        today = "Sunday" 
      Case 1 
        today = "Monday"
      Case 2 
        today = "Tuesday" 
      Case 3
        today = "Wednesday"
      Case 4 
        today = "Thursday" 
      Case 5 
        today = "Friday" 
      Case 6 today = "Saturday" 
    End Select
    
    If startTime <> 0 And endTime <> 0 Then 
      If startTime <> lastStartTime Or endTime <> lastEndTime Then 
        If Len(results) > 1 Then 
          results = results + Chr(10) 
        End If 
        
        results = results + today 
      Else If startTime <> nextStartTime Or endTime <> nextEndTime Then
        results = results + " - " + today
      End If 
      
      If startTime <> nextStartTime Or endTime <> nextEndTime Then 
        results = results + Chr(10) 
        startTime = startTime - 1 
        
        If startTime <> 0 And endTime <> 23 Then 
          If startTime > 12 Then 
            results = results + CStr(startTime - 12) + ":00 pm" 
          Else If startTime = 0 Then 
            results = results + "12:00 am" 
          Else 
            results = results + CStr(startTime) + ":00 am" 
          End If 
        
          results = results + " to "
          endTime = endTime - 1 
          
          If endTime > 12 Then 
            results = results + CStr(endTime - 12) + ":00 pm"
          Else If endTime = 0 Then 
            results = results + "12:00 am" 
          Else 
            results = results + CStr(endTime) + ":00 am" 
          End If 
        Else 
          results = results + "All Day"
        End If 
      End If 
    End If 
  Next 
  
  Return results 
End Function

What it returns is a string, specially formatted for the particulars of my report. The nice thing about it, is that it summarizes the data. If the times blocked out on the calendar are identical for consecutive days, it prints the range of dates in the format "[Start Date] - [End Date]" and places the effective times underneath it in the format "[Start Time] to [End Time]". If there is variance in the effective times on following dates, it prints the unique start and end times for each date.

This may not be the most efficient, or portable method of converting the "effectivitycalendar" data into readable form, but it works nicely for my implementation and allows for a great deal more flexibility than I had. It should be noted that this code assumes all time points between the start and the end for any given day are filled in. This code would need heavy modification to accommodate several time-ranges for a single day.