View Single Post
 
Old 05-04-2016, 09:51 AM
carlos_cs carlos_cs is offline Windows 10 Office 2013
Novice
 
Join Date: May 2016
Posts: 6
carlos_cs is on a distinguished road
Default Timeline - fill sheet with the missing dates between given interval

I have the following Excel problem:


I currently have this in sheet1 columns A,B,C:


Date Time Worker
04/04/2016 4,5 John
05/04/2016 2 John
06/04/2016 6,5 John
07/04/2016 0 --
08/04/2016 0,5 Charles
08/04/2016 2 John
08/04/2016 0,5 William
09/04/2016 0 --
10/04/2016 0 --
11/04/2016 9 John
11/04/2016 3,75 William


I also have:
The Creation date of Project In cell E1 : 28/03/2016
The Actual start of Project In cell E2: 29/03/2016
The Today date In cell E3 (let's consider today date is the one i show next): 13/04/2016


What I want in sheet2:

Choose the earliest date between Creation and Actual start of Project, which in this case is 28/03/2016
Then add the missing dates with 0 Time and -- Worker until it reaches the Today date.
It should look like this after all done:

Date Time Worker
28/03/2016 0 --
29/03/2016 0 --
30/03/2016 0 --
31/03/2016 0 --
01/04/2016 0 --
02/04/2016 0 --
03/04/2016 0 --
04/04/2016 4,5 John
05/04/2016 2 John
06/04/2016 6,5 John
07/04/2016 0 --
08/04/2016 0,5 Charles
08/04/2016 2 John
08/04/2016 0,5 William
09/04/2016 0 --
10/04/2016 0 --
11/04/2016 9 John
11/04/2016 3,75 William
12/04/2016 0 --
13/04/2016 0 --


It is the continuation of a problem i had but only detected now:
excel - Timeline - loop through all dates between first and last given and add date to column if not found - Stack Overflow
Looking for a excel-vba macro solution because i believe it's the only way to do it.
I'm new to VBA and stuck with this problem and all the help means a lot to me!
Reply With Quote