0
I have some data which has been extracted from staff timesheets which provides number of hours worked by a member of staff on a given day. I need to summarise this information grouping by days but only incrementing the start day by 1 each time not the number of days I am grouping by.
For example if I group by number of days=2 I get results of the form (using UK date format btw)
Date Hours 29/07/2014 - 30/07/2014 xx 31/07/2014 - 01/08/2014 xx 04/08/2014 - 05/08/2014 xx 06/08/2014 - 07/08/2014 xx 08/08/2014 - 09/08/2014 xx
What I actually want is
Date Hours 29/07/2014 - 30/07/2014 xx 30/07/2014 - 31/07/2014 xx 31/07/2014 - 01/08/2014 xx 01/08/2014 - 02/08/2014 xx 02/08/2014 - 03/08/2014 xx
In other words the start date only increments by one day at a time rather than by the number of days being grouped by.
I can't seem to find the pivot table magic needed to implement this so is something like this possible?
You want your data to overlap except the first day and the last day using your example of 2 day? – Sun – 2014-09-15T16:57:57.427
Yes that's right – empedia – 2014-09-15T17:09:43.963
Thanks for this, I'm not sure I understand exactly what you are saying - when you have a date in column a and a number representing hours in column b - sum(a2+b2) actually just increments the date in column a by the number of days in column b. Do you mean sum(b2+b3) to sum the hours. I tried something like this but I don't see how it will work with pivot tables. – empedia – 2014-09-15T17:50:37.567
I don't think this will work with pivot tables as they are basically designed to classify a row and put it somewhere once, not multiple times like this would have to do. – gtwebb – 2014-09-15T17:53:49.160