Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a date range, Start date and End date and total hours for that range per job.
What i have done is taken total hours/(End date-Start date) to create an average for each line. I would like to auto generate the dates between start and end dates.
For example this is the table I have.
JOB#, Start date, End date, Total hours, Total daily hours
12345, 5/10/16, 5/20/16, 100, 10
12346, 5/12/16, 5/22/16, 50, 5
What I would like to see is:
JOB#, Date, Total hours, Total Daily Hours
12345, 5/10/16, 100, 10
12345, 5/11/16, 100, 10
12345, 5/12/16, 100, 10
12345, 5/13/16, 100, 10
12345, 5/14/16, 100, 10
12345, 5/15/16, 100, 10
....
12345, 5/20/16, 100, 10
12346, 5/12/16, 50, 5
12346, 5/13/16, 50, 5
....
12346, 5/22/16, 50, 5
any help is greatly appreciated. Thank you.
Try this:
Table:
LOAD JOB#,
Date([Start date] + IterNo() - 1) as Date,
[Total hours],
[Total daily hours]
While [Start date] + IterNo() - 1 <= [End date];
LOAD * Inline [
JOB#, Start date, End date, Total hours, Total daily hours
12345, 5/10/16, 5/20/16, 100, 10
12346, 5/12/16, 5/22/16, 50, 5
];
Try this:
Table:
LOAD JOB#,
Date([Start date] + IterNo() - 1) as Date,
[Total hours],
[Total daily hours]
While [Start date] + IterNo() - 1 <= [End date];
LOAD * Inline [
JOB#, Start date, End date, Total hours, Total daily hours
12345, 5/10/16, 5/20/16, 100, 10
12346, 5/12/16, 5/22/16, 50, 5
];
Thank you!
Hello Sunny,
I hope this message finds you well.
I would like to recreate each row per day (screenshot below) and excel file in attachment:
What is the best approach?
Thank you very much for your help.
Kind regards,
Nicolas.