Macro to add as many rows as difference between start date and end date

[JP01]

Wondering if this has ever been 'invented'....

I have a list of tasks, that contains a taskID, start date, end date, person to perform the task and number of hours that it should take to perform the task:

TASK WHO STARTDATE ENDDATE HOURS
0001 John 10/10/2008 10/15/2008 32
0002 Mary 10/10/2008 10/10/2008 8
0003 Chris 10/13/2008 10/14/2008 10

What I need is a macro that can add rows for each working day (so split up every row in one row per working day). The number of hours needs to be divided evenly over the rows. In this case there are 4 working days (10/10/08 is a Friday) for John, 1 working day for Mary and 2 for Chris. Hence, the result of the macro should be:

TASK WHO STARTDATE ENDDATE HOURS
0001 John 10/10/2008 10/10/2008 8
0001 John 10/13/2008 10/13/2008 8
0001 John 10/14/2008 10/14/2008 8
0001 John 10/15/2008 10/15/2008 8
0002 Mary 10/10/2008 10/10/2008 8
0003 Chris 10/13/2008 10/13/2008 5
0003 Chris 10/13/2008 10/14/2008 5

Can anybody help?

 

[ravishankar]

Hi
try the following codes

Sub JP01()
Dim x As Long, d As Long, a As Long, b as long
d = 1
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
Cells(1, 6) = "=Datedif(B" & a & " ,C" & a & ",""D"")"
For b = 1 To Cells(1, 6)
Sheets("Sheet2").Cells(d, 1) = Cells(a, 1)
Sheets("Sheet2").Cells(d, 2) = Cells(a, 2)
Sheets("Sheet2").Cells(d, 3) = Cells(a, 3)
Sheets("Sheet2").Cells(d, 4) = Cells(a, 4)
Sheets("Sheet2").Cells(d, 5) = Cells(a, 5) / Cells(1, 6)
d = d + 1
Next b
Next a
End Sub

Ravi

 

[JP01]

Thanks! Works almost, but not quite...

It looks only at the top line to determine how many lines it should create, so also for Mary and Chris, 4 lines are created, while they have 1 and 2 working days respectively.

I added two more columns to make is more simple: The column working days and the column: hours per working day. Therefore, the basis is as follows:

TASK WHO STARTDATE ENDDATE HOURS WORKDAYS HRS/DAY
0001 John 10/10/2008 10/15/2008 32 4 8
0002 Mary 10/10/2008 10/10/2008 8 1 8
0003 Chris 10/13/2008 10/14/2008 10 2 5

The result of the macro should of course be the same.

 

[ravishankar]

Hi
Just replace with this line

Cells(1, 6) = "=Datedif(C" & a & " ,D" & a & ",""D"")"

It should work
Ravi

 

[JP01]

Great!

One last adaptation that is needed:
The startdate should 'count forward' to the next working day.

So currently, startdate remains 10/10 for John in sheet 2, but should be (in the 4 consecutive rows):
10/10/08
10/13/08
10/14/08
10/15/08
I would not be bothered if October 11th and 12th (weekend) would be present, but the number of hours should be 0 on these rows.