# calculate total hours based on dates

cpliu
Guest
Posts: n/a

 13th Jul 2012
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

cpliu
Guest
Posts: n/a

 13th Jul 2012
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

Alex Plantema
Guest
Posts: n/a

 13th Jul 2012
cpliu schreef in news:b2966474-9525-4c96-89a0-(E-Mail Removed)

> 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

You compare dates with project names.

--
Alex.

cpliu
Guest
Posts: n/a

 17th Jul 2012
On Friday, July 13, 2012 1:08:13 PM UTC-4, Alex Plantema wrote:
> You compare dates with project names.
>

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

Thanks,

Alex Plantema
Guest
Posts: n/a

 19th Jul 2012
cpliu schreef in news:38a095be-9c9c-4bc9-9d02-(E-Mail Removed)

> 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

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.

--
Alex.

cpliu
Guest
Posts: n/a

 21st Sep 2012
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.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Alaska1 Microsoft Access 1 27th May 2010 10:04 PM Pablito Microsoft Access Forms 0 28th Aug 2008 11:46 PM =?Utf-8?B?R3JhaGFt?= Microsoft Excel Misc 2 28th Jan 2007 09:40 PM =?Utf-8?B?dHJ5aW5ndG9sZWFybg==?= Microsoft Excel Misc 1 19th Nov 2006 11:24 PM StargateFan Microsoft Excel Misc 8 7th Jan 2006 08:35 PM

Features