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?
empedia
Posted 2014-09-15T16:53:09.527
Reputation: 1
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