Apr 09 2019 03:39 AM
I am trying to analyze time spent in four different activities realized everyday. Please see attachd excel sheet.
I'm trying to use "Dynamic tables" in order to get time spent per day in minutes and also as a percentage of time spent per day. I would also like to have the average time spent on a weekly, monthly and yearly basis.
Unfortunately I have tried to do it myself but due to formatting cells I am unable.
I wonder if you could once again help me.
Thank you very much in advance, Juan
Apr 09 2019 05:12 AM
Hi Juan,
To apply proper format right click on any cell in the column, click on Value Field Settings and apply proper format. Elapsed time in minutes will be like
Same for per cent.
Also you may select here aggregation (sum, average, etc.)
To have figures by weeks you need to add one more column with week numbers into your source range, like
=WEEKNUM(B2,2)
and group by it (in additional to year and month).
Apr 17 2019 06:58 AM
Dear Sergei, thank you very much for the table and help.
I have taken some time to answer back as this is new for me and I need a lot of time to understand and avance.
I hope if you don't mind that I keep on asking in a few weeks once I have all my questions together.
I wish you a nice week, Juan.
Apr 17 2019 09:53 AM
Hi Juan,
Sure, don't hesitate to ask any question. I don't promise immediate answers, there are periods when I'm overloaded by other stuff. And there is always a chance if someone else comments, lot of great experts are here.
Apr 18 2019 04:24 AM
Thank you very much for your disposal to help.
I have being testing myself with the format options and some other features.
Could you tell me :
Apr 18 2019 09:33 AM
Hi Juan,
One by one
If you right click on the column with dates in PivotTable and select Grouping when it'll be added all your grouped columns (months, etc)
Apr 18 2019 09:53 AM
You may add one more column to your source data, in simplest variant
=IF(WEEKDAY(B2,2)<6,"Weekday", "Weekend")
and add it to you hierarchy in Pivot Table
Apr 18 2019 10:01 AM
That's just sum of minutes for all records in the period divided on number of such records, I don't know what that's all about and does it have any sense from business logic point of view.
Yes, just add one more grouping by years
Apr 19 2019 02:45 AM
Once again your help has been very useful for understanding pivot tables and taking advantage of my data sheet. Thank you very much indeed.
The only field that I still cannot understand and develop properly is the “average” funtion. I would like to know how to calculate averages of daily, weekly, monthly and year activities...
What would you suggest or recommend to understand and learn this area?
Apr 19 2019 09:13 AM
Hi Juan,
Average is calculated as sum of all values divided on count of all values under the level. If you have sub-levels, the average of the level is not the average of all sub-levels, it's the average of all items under it.
Let take this grouping for AAA
If you take Weekdays average it is not equal to average of values for 01-04 Jan (which is 34:46). They itslef are also averages for the each of the day.
If you take min for all AAA from 01 to 04 Jan in [mm]:ss format and take the average from it you'll have exactly the same what is in PivotTable for that level
Apr 20 2019 03:00 AM
Thank you again for your quick answer and clear example. Now I have understand the way it works!!
However what I am looking for, is the average of the each activity (AAA/BBB/CCC/DDD ) on the whole day, week, month or year.
Do you have an idea of how this could be calculated?
Apr 20 2019 05:12 AM
@juan jimenez , perhaps I misunderstood something, but you already have minutes average in this column
Apr 22 2019 02:18 AM
You are right. It was all there. Now I have understood everything.
Thank you very much for your constant help and patience.
Congratulations, Juan.