calculate total hours based on dates

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

I have the following columns:
Date Start,Date End,Project,Hour
I'd like to input the project name in multiple rows, enter a range to cover in date start and date end, then it will calculate the total hours for each project used in that period of time.
I had the following formula working based on the range of rows to calculate, but what change do I need in order to add up based on the dates?

=SUMPRODUCT(($B$1768:$B$1793=C2)*($E$1768:$E$1793))
B column is the projects entered in the column
E is the time spent on project in B column
C2 is the project name entered to match.

Thanks,

cpliu
 
What's wrong with the following, I got 0 for the total of each row.

=SUMPRODUCT(--(Data!$B$3:$B$7000>=A3),--(Data!$B$3:$B$7000<=B3),--(Data!$B$3:$B$7000=C3),(Data!$E$3:$E$7000))

B column: project names
A3 = start date
B3 = end date
E column = time spent on each project entry
 
cpliu schreef in
What's wrong with the following, I got 0 for the total of each row.

=SUMPRODUCT(--(Data!$B$3:$B$7000>=A3),--(Data!$B$3:$B$7000<=B3),--(Data!$B$3:$B$7000=C3),(Data!$E$3:$E$7000))

B column: project names
A3 = start date
B3 = end date
E column = time spent on each project entry
Click to expand...

You compare dates with project names.
 
You compare dates with project names.
Click to expand...
Thank you alex. I thought what it means was to find all the column B data between dates in A3 and B3, put the total hours for the project name in B3.

Use the example below:
1. data in Data worksheet.
date, project name, hours spent
--------------------------------
7/9/2012,project 1,1.5
7/9/2012,project 2,1
7/9/2012,project 3,4
7/10/2012,project 4,1.5
7/10/2012,project 2,3
7/10/2012,project 3,2
7/11/2012,project 1,4.5
7/12/2012,project 2,3
7/12/2012,project 3,2
7/12/2012,project 4,2
7/13/2012,project 2,5.5

I'd like to find out total hours worked on each project for the past week. use the example above, I'd like to get the numbers in the hours column like the one below:
date start,date end,project,hours
----------------------------------
7/9/2012,7/13/2012,project 1,6
7/9/2012,7/13/2012,project 2,12.5
7/9/2012,7/13/2012,project 3,8
7/9/2012,7/13/2012,project 4,3.5

Please advise the right formula to get these numbers.

Thanks,
 
cpliu schreef in
Use the example below:
1. data in Data worksheet.
date, project name, hours spent
--------------------------------
7/9/2012,project 1,1.5
7/9/2012,project 2,1
7/9/2012,project 3,4
7/10/2012,project 4,1.5
7/10/2012,project 2,3
7/10/2012,project 3,2
7/11/2012,project 1,4.5
7/12/2012,project 2,3
7/12/2012,project 3,2
7/12/2012,project 4,2
7/13/2012,project 2,5.5

I'd like to find out total hours worked on each project for the past
week. use the example above, I'd like to get the numbers in the hours
column like the one below: date start,date end,project,hours
----------------------------------
7/9/2012,7/13/2012,project 1,6
7/9/2012,7/13/2012,project 2,12.5
7/9/2012,7/13/2012,project 3,8
7/9/2012,7/13/2012,project 4,3.5
Click to expand...

It's probably easier to use a pivot table, though the layout of the result may be a bit different.
Select the table, start the pivot table wizard,
drag the date to the rows field, the projects to the columns field,
and the hours to the data field.
 
Thank you for your help and suggestion on pivot table (learning more on it now). You are correct that I compare the name to the date, once changed, it's working.
 
Back
Top