Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula to track time against projects within a date range.
Sum Hours (Timesheet!G:G) based on "Activity" (Timesheet!C:C to match against Reporting!B14); within date range (Reporting!>B49 &
Solved by I. H. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
18/07/2018 - 06:30
Reporting
Excelchat Expert
18/07/2018 - 06:30
Hi
User
18/07/2018 - 06:30
Hi
Excelchat Expert
18/07/2018 - 06:31
Could you please elaborate on the problem
Excelchat Expert
18/07/2018 - 06:31
It would be really helpgul if i can get a look at the sheets
Excelchat Expert
18/07/2018 - 06:31
*helpful
User
18/07/2018 - 06:32
ok, I have just copied them into the spreadsheet here
User
18/07/2018 - 06:33
So I am looking to match data included on "timesheet" with the project and date range on "reporting"
Excelchat Expert
18/07/2018 - 06:33
thanks
Excelchat Expert
18/07/2018 - 06:33
let me have a look
User
18/07/2018 - 06:33
Thank you! :)
Excelchat Expert
18/07/2018 - 06:35
Please help me understand what exactly you need
User
18/07/2018 - 06:36
Well, I need to figure out what formula will help me to analyse the data entered into "timesheet"
User
18/07/2018 - 06:37
So I need to enter something starting at C14 that counts how many hours have been spent working on 1:1 Positive People within the date range 25-5-18 to 14-6-18
Excelchat Expert
18/07/2018 - 06:37
okay
Excelchat Expert
18/07/2018 - 06:38
Should this be a different table, where you have activity, start date , end date and time spent in hours
Excelchat Expert
18/07/2018 - 06:38
?
User
18/07/2018 - 06:40
You mean like the "timesheet" one? where they enter Date ; Project and Hours?
Excelchat Expert
18/07/2018 - 06:41
No, just aggregate of the table in timesheet
Excelchat Expert
18/07/2018 - 06:41
You can chnage the date range ad the formula should show time spent accordingly
User
18/07/2018 - 06:41
hmmm what might that look like? And I guess I would still need to same formula to transfer the data from "timesheet" to "reporting"?
Excelchat Expert
18/07/2018 - 06:43
I am getting confused here... my understanding is you want analyze the data in timesheet by looking at how many hours where spent working on a particular activity during a time frame
Excelchat Expert
18/07/2018 - 06:43
Is my understanding correct?
User
18/07/2018 - 06:43
yes that's right and I want that to be shown on the reporting page
Excelchat Expert
18/07/2018 - 06:44
So, from the timesheet sheet, you want to populate the table in reporting tab
Excelchat Expert
18/07/2018 - 06:44
correct?
User
18/07/2018 - 06:44
yes :) so the hours need adding up in relation to each project on the reporting page
Excelchat Expert
18/07/2018 - 06:45
Easier way would be to use pivot table where do don't have to create formula
Excelchat Expert
18/07/2018 - 06:45
It can look be formatted as in the reporting tab
Excelchat Expert
18/07/2018 - 06:45
Should I go ahead with this?
User
18/07/2018 - 06:46
yes please, Ive never used pivot tables
Excelchat Expert
18/07/2018 - 06:46
Give me few minutes while work on it
User
18/07/2018 - 06:46
ok
Excelchat Expert
18/07/2018 - 06:51
I have created a new sheet Reporting Pivot
[Uploaded an Excel file]
User
18/07/2018 - 06:52
Thank you! :)
Excelchat Expert
18/07/2018 - 06:52
I hope this solves your problem
Excelchat Expert
18/07/2018 - 06:52
The value in the pivot table is dependent on the data in timesheet tab
Excelchat Expert
18/07/2018 - 06:53
Once you update the timesheet data, the only other thing you would have to do is refresh pivot table
User
18/07/2018 - 06:53
ok brilliant so does that mean that as additional project are added this will automatically be reflected in the pivot table?
Excelchat Expert
18/07/2018 - 06:53
Yes, but you would have to refresh the pivot table
Excelchat Expert
18/07/2018 - 06:54
Let me show you how
Excelchat Expert
18/07/2018 - 06:54
Select a cell on the pivot table > Analyze Tab > Refresh > Refresh
Excelchat Expert
18/07/2018 - 06:55
You find analyze tab on the top of the excel window
Excelchat Expert
18/07/2018 - 06:55
Did you follow?
User
18/07/2018 - 06:55
Got it, that's fantastic!
User
18/07/2018 - 06:56
Thanks so much, I have been working on this for hours - you've been so helpful!
Excelchat Expert
18/07/2018 - 06:57
you are welcome
Excelchat Expert
18/07/2018 - 06:57
Please provide feedback
User
18/07/2018 - 06:57
Will do, thanks again! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
