Spilt a date range into one row per day

[liorchn]

Hi there,

Can you please help me (I guess with a macro code) how to split a date range into one day per row.

Ive got an excel sheet with 30,000 rows which contains an absence leaves of the company employees (emp number, emp name, absence code, start date, end date).

I want that every row contain only one day and not a date range (I know that this will lead to the addition of many roes but i dont have a choise)

Thank you very much​

 

[mole999]

i think you might need to posts some of the data, and what it should look like

I guess that on some days you will have more than one worker off, do they get a line each

when they are off, do you use the first day off to day back, do you / will you over count planned days off as sickness

 

[liorchn]

i think you might need to posts some of the data, and what it should look like

I guess that on some days you will have more than one worker off, do they get a line each

when they are off, do you use the first day off to day back, do you / will you over count planned days off as sickness

I dont know how to upload a file to the message but ill try to explain through your qwestions :

"I guess that on some days you will have more than one worker off, do they get a line each" = yes

"when they are off, do you use the first day off to day back (Yes)

do you / will you over count planned days off as sickness - Im not sure that i understand the question, but in general all the absent types are counted

Thank you very much

 

[mole999]

Im not sure that i understand the question, but in general all the absent types are counted

If a worker is off on the Monday and stays off for two weeks returning on the Monday, are they counted as Ten Days (because they get two days off per week) or fourteen days

 

[Peter_SSs]

Can you please help me (I guess with a macro code) how to split a date range into one day per row.

Ive got an excel sheet with 30,000 rows which contains an absence leaves of the company employees (emp number, emp name, absence code, start date, end date).

I want that every row contain only one day and not a date range

Welcome to the MrExcel board!

See if this is what you want. Test in a copy of your workbook.

Sub One_Day_Per_Row()
  Dim a, b
  Dim rws As Long, sr As Long, i As Long, j As Long, k As Long, r As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
  rws = UBound(a, 1)
  For r = 1 To rws
    a(r, 6) = a(r, 5) - a(r, 4) + 1
    k = k + a(r, 6)
  Next r
  If k < Rows.Count Then
    ReDim b(1 To k, 1 To 4)
    sr = 1
    For r = 1 To rws
      For i = 0 To a(r, 6) - 1
        For j = 1 To 3
          b(sr + i, j) = a(r, j)
        Next j
        b(sr + i, 4) = a(r, 4) + i
      Next i
      sr = sr + a(r, 6)
    Next r
    Range("G2").Resize(k, 4).Value = b
    Range("G1:J1").Value = Array("emp number", "emp name", "absence code", "date")
  Else
    MsgBox "Too many rows"
  End If
End Sub

Starting with the data in columns A:E below, the code has produced what you see in columns G:J. Is that what you want?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	emp number	emp name	absence code	start date	end date		emp number	emp name	absence code	date
2	A101	Name1	x	25-Apr-14	02-May-14		A101	Name1	x	25-Apr-14
3	X765	Name2	y	03-Mar-14	03-Mar-14		A101	Name1	x	26-Apr-14
4	A101	Name1	y	07-May-14	09-May-14		A101	Name1	x	27-Apr-14
5	B888	Name3	z	28-Dec-13	05-Jan-14		A101	Name1	x	28-Apr-14
6							A101	Name1	x	29-Apr-14
7							A101	Name1	x	30-Apr-14
8							A101	Name1	x	01-May-14
9							A101	Name1	x	02-May-14
10							X765	Name2	y	03-Mar-14
11							A101	Name1	y	07-May-14
12							A101	Name1	y	08-May-14
13							A101	Name1	y	09-May-14
14							B888	Name3	z	28-Dec-13
15							B888	Name3	z	29-Dec-13
16							B888	Name3	z	30-Dec-13
17							B888	Name3	z	31-Dec-13
18							B888	Name3	z	01-Jan-14
19							B888	Name3	z	02-Jan-14
20							B888	Name3	z	03-Jan-14
21							B888	Name3	z	04-Jan-14
22							B888	Name3	z	05-Jan-14
23
One day per row

 

[apo]

Hi..

This should work as well...

Private Sub CommandButton1_Click()
Dim Lr As Long, trCnt As Integer, trVal, cell
    With Sheets("Sheet1")
        For Each cell In .Range("A2:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
        Lr = .Range("J" & Rows.Count).End(xlUp).Row + 1


            trCnt = DateDiff("d", cell.Offset(, 3), cell.Offset(, 4)) + 1
            trVal = Array(cell.Value, cell.Offset(, 1).Value, cell.Offset(, 2).Value)
            cell.Offset(Lr - cell.Row, 9).Resize(trCnt, 3).Value = trVal
            cell.Offset(Lr - cell.Row, 12).Value = cell.Offset(, 3).Value


            cell.Offset(Lr - cell.Row, 12).Resize(trCnt).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
                                                       xlDay, Step:=1, Stop:=cell.Offset(, 4).Value, Trend:=False
        Next cell
    End With
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:75px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:88px;" /><col style="width:74px;" /><col style="width:96px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:31px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#333333; font-weight:bold; font-family:Arial; font-size:10pt; ">emp number</td><td style="color:#333333; font-weight:bold; font-family:Arial; font-size:10pt; ">emp name</td><td style="color:#333333; font-weight:bold; font-family:Arial; font-size:10pt; ">absence code</td><td style="color:#333333; font-weight:bold; font-family:Arial; font-size:10pt; text-align:right; ">start date</td><td style="color:#333333; font-weight:bold; font-family:Arial; font-size:10pt; text-align:right; ">end date</td><td style="color:#333333; font-weight:bold; font-family:Arial; font-size:10pt; "> </td><td > </td><td > </td><td > </td><td style="font-weight:bold; text-align:center; ">Emp Number</td><td style="font-weight:bold; text-align:center; ">Emp Name</td><td style="font-weight:bold; text-align:center; ">Absence Code</td><td style="font-weight:bold; text-align:center; ">Date</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">A101</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">Name1</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">x</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">25-Apr-14</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">2-May-14</td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">25-Apr-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">X765</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">Name2</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">y</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">3-Mar-14</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">3-Mar-14</td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">26-Apr-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">A101</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">Name1</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">y</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">7-May-14</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">9-May-14</td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">27-Apr-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">B888</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">Name3</td><td style="color:#333333; font-family:Arial; font-size:10pt; ">z</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">28-Dec-13</td><td style="color:#333333; font-family:Arial; font-size:10pt; text-align:right; ">5-Jan-14</td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">28-Apr-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">29-Apr-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">30-Apr-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">1-May-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >x</td><td style="text-align:right; ">2-May-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >X765</td><td >Name2</td><td >y</td><td style="text-align:right; ">3-Mar-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >y</td><td style="text-align:right; ">7-May-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >y</td><td style="text-align:right; ">8-May-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >A101</td><td >Name1</td><td >y</td><td style="text-align:right; ">9-May-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">28-Dec-13</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">29-Dec-13</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">30-Dec-13</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">31-Dec-13</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">1-Jan-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">2-Jan-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">3-Jan-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">4-Jan-14</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >B888</td><td >Name3</td><td >z</td><td style="text-align:right; ">5-Jan-14</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Peter_SSs]

This should work as well...

Andrew, you are probably right, but did you note that the OP has 30,000 rows of this data and did you test your code on anything larger than your posted screen shot? I don't suggest you start testing at 30,000 but might be worth looking at how it goes with, say, 500 or 1000 rows to get an idea of speed.

 

[apo]

Good point Peter..

I did a quick test with 5000 rows and it did it in what 'seemed' like under a second..

Not a very accurate test.. i know.. and i can see how your code is only reading once and writing once.. and all the loops are done within memory..

Whereas.. mine is writing to the sheet each time..

I wonder if my method could be done within memory.. I will try that tomorrow..

 

[liorchn]

Good point Peter..

I did a quick test with 5000 rows and it did it in what 'seemed' like under a second..

Not a very accurate test.. i know.. and i can see how your code is only reading once and writing once.. and all the loops are done within memory..

Whereas.. mine is writing to the sheet each time..

I wonder if my method could be done within memory.. I will try that tomorrow..

Thank you very much (both of you)

 

[VoG]

Cross posted Spilt a date range into one row per day

Please read Excelguru Help Site - A message to forum cross posters