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

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). juan jimenez

SergeiBaklan 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.

juan jimenez 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.

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 : How did you manage to introduce “meses” on “promedios” sheet if this is not an existing column in the data source? Is there a way to split weekdays and weekends so that average of working or resting time is more accurate? What would it mean the average of minutes as it seems not to have any sense in this case?If I want to know the total for every year, do I have to introduce a column in the data sheet for the year number?

Hi Juan, One by one How did you manage to introduce “meses” on “promedios” sheet if this is not an existing column in the data source? 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)

Dynamic tables | Microsoft Community Hub

13 Replies

SergeiBaklan
Diamond Contributor
Apr 09, 2019
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).

juan jimenez
TR19_v190408trisComunity.xlsx483 KB
- juan jimenez
  Iron Contributor
  Apr 17, 2019
  SergeiBaklan
  
  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.
  - SergeiBaklan
    Diamond Contributor
    Apr 17, 2019
    juan jimenez
    
    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.

Forum Discussion

Dynamic tables

13 Replies

Resources