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