Average time spent on Activities

[Gaurang Mhatre]

hi everyone,

Need your help in creating report for attached data for Team review.

1) Calculate average time spent on each activity on basis of category

2) Overall how much time spent on activities

What will be the formula to calculate Average time for each and overall activity.

Thanks for your help in advance.

Regards,
Gaurang

 

[Ashhu]

Dear Gaurav
Please find attached sheet with table in S:T:U column, hope you are looking for somethis like this. let me know if my understanding is wrong.

PS: i see few END Date creating issue and sowing more hours in first category, please edit those values.

 

[Gaurang Mhatre]

Dear Aashu,

Thanks for help.bt have some query.. hw u have calculated average
and how you will calculate avg time spent weekly and monthly?

if you could help me in formula...

Basically we want to see how employee is spending time on each activity (category) whether he has work or not

Thanks a ton once again for your time.

Regards,
Gaurang

 

[Ashhu]

Dear Gaurang,
Please find attached sheet.
I added new table below previous table to find week data.
to assist this i have included new column "Week" number to associate with formula.
hope thats fine.

Please check and let me know further assistance.

 

[Ashhu]

gaurang
you can see DIV error because of there is not data match. to avoid , follow below thread. write back if you need anything else.

http://forum.chandoo.org/threads/div-0-error.29167/

meantime
Dear @Khalid NGO please have a look once at solution, your suggestion to Gurang table is appreciated.

 

[Khalid NGO]

Hi Ashwin,

I think you are on track, and yes the #DIV/0! error is because there is no Client Queries and Con call in week 5.

Regards,

 

[Gaurang Mhatre]

Hi Asshu,

Thanks.
But, how to calculate monthly average time taken?

Regards,
Gaurang

 

[Ashhu]

Please find attached file with your requirement.

-Regards

 

[Gaurang Mhatre]

Thanks alot aashu...god bless u

 

[Ashhu]

GM Gaurang
did you create new column for weeknum, if you see my second attachment, Q column read weeknum. which you dont have in last attachment.
Try and if not solved, upload the sheet please

Good day!

 

[Gaurang Mhatre]

thanks it worked....thanks a lot for help