Excel VBA convert date period to date row

This Excel VBA tutorial explains how to convert date period to date row.

Excel VBA convert date period into date row

Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period

Begin Date	End Date
1/1/2018	1/3/2018

However some system store one date per row.

Begin Date	End Date
1/1/2018	1/1/2018
2/1/2018	2/1/2018
3/1/2018	1/3/2018

To convert from one format to another is extremely difficult and time consuming. In this tutorial, I will demonstrate how to convert date period to date row, because I personally think this data structure is easier to handle.

VBA code – convert date period to date row

Press ALT+F11, insert the code into a Module.

Public Sub convertDate()
  beginDtCol = "B"  'column that contains begin date period
  endDtCol = "C"   'column that contains end date period
  For r = Range(beginDtCol & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range(endDtCol & r) - Range(beginDtCol & r) > 0 Then
      For i = 1 To Range(endDtCol & r) - Range(beginDtCol & r)
        Rows(r).EntireRow.Copy
        Range("A" & Range(beginDtCol & Rows.Count).End(xlUp).Row + 1).Select
        ActiveSheet.Paste
 
        Range(beginDtCol & Range(beginDtCol & Rows.Count).End(xlUp).Row) = Range(beginDtCol & r) + i
        Range(endDtCol & Range(endDtCol & Rows.Count).End(xlUp).Row) = Range(beginDtCol & r) + i
      Next i
      Range(endDtCol & r) = Range(beginDtCol & r)
    End If
  Next
  Range("A1").AutoFilter
  'sort column A
  ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  ActiveSheet.AutoFilter.Sort.Apply
End Su

 

Example – convert date period to date row

Suppose we have two leave transactions below.

 

Run the Macro, date period is converted to date row. In addition, all the other column values are copied.

Column A is also sorted in ascending order. Change the code at the bottom if you want to sort another column.