sum hours and minutes with Excel 2000
 

During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

Bill

sum hours and minutes with Excel 2000
 

Hi Bill

Can you post some sample data, so we can see how your data are laid out.

Is hours and minutes entered in the format t:mm ?
Do you have a date column where dates are entred for each hour/minutes
record?

Regards,
Per

"bill" skrev i meddelelsen
...
During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week.
I
have Excel 2000 and I would like to add up the total number of hours spent
in
each week.

Bill

sum hours and minutes with Excel 2000
 

You want a pivot table. See this website for help:
http://www.contextures.com/xlPivot07.html

Regards,
Fred.

"bill" wrote in message
...
During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week.
I
have Excel 2000 and I would like to add up the total number of hours spent
in
each week.

Bill

sum hours and minutes with Excel 2000
 

do you have the cells set as 2:03 or do you have one cell with 2 and one with
3?

If the former, merely do =SUM(A2:A50) and format your result cell under
Custom as [h]:mm. If the latter, do a sum of the hours divided by 24 and a
sum of the minutes divided by 1440, then add those cells together and format
as [h]:mm

"bill" wrote:

During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

Bill

sum hours and minutes with Excel 2000
 

Say we have data in cols A & B like:

Thursday, January 01, 2009 06:45
Friday, January 02, 2009 05:01
Saturday, January 03, 2009 01:07
Sunday, January 04, 2009 00:52
Monday, January 05, 2009 04:44
Tuesday, January 06, 2009 00:44
Wednesday, January 07, 2009 05:36
Thursday, January 08, 2009 01:23
Friday, January 09, 2009 01:43
Saturday, January 10, 2009 03:28
Sunday, January 11, 2009 01:31
Monday, January 12, 2009 00:50
Tuesday, January 13, 2009 03:07
Wednesday, January 14, 2009 00:46
Thursday, January 15, 2009 02:04
Friday, January 16, 2009 02:11
Saturday, January 17, 2009 04:34
Sunday, January 18, 2009 02:55
Monday, January 19, 2009 03:38
Tuesday, January 20, 2009 04:34
Wednesday, January 21, 2009 04:45
Thursday, January 22, 2009 01:03
Friday, January 23, 2009 07:03
Saturday, January 24, 2009 06:24
Sunday, January 25, 2009 07:11
Monday, January 26, 2009 04:03
Tuesday, January 27, 2009 06:57
Wednesday, January 28, 2009 04:01
Thursday, January 29, 2009 04:05
Friday, January 30, 2009 06:22
Saturday, January 31, 2009 06:09

In C1 enter:

=WEEKNUM(A1) and copy down

In D1 enter:

=SUMPRODUCT((B$1:B$365)*(C$1:C$365=ROW())) and copy down thru D52

Correctly formatted column D will give the weekly sums:

12:54
18:35
15:07
06:24
14:52
11:48
17:20
08:17
23:16
02:23
20:32
22:14


--
Gary''s Student - gsnu200907


"bill" wrote:

During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

Bill

sum hours and minutes with Excel 2000
 

format your cells to [h]:mm:ss.
(right click cell - format cells - custom - [h]:mm:ss bottom of the list.)


"bill" wrote:

During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

Bill

sum hours and minutes with Excel 2000
 

just set your cell format to [h]:mm:ss
its at the bottom of the custom format list

"bill" wrote:

During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

Bill

sum hours and minutes with Excel 2000
 

Nothing could be simpler, Bill. If you have them recorded in Excel's notion
of time, ie if they're displayed using one of the time formats like "[h]:mm",
then you can simply sum them up. If the sum shows up looking like a date (eg
"January 13, 1900, 09:02"), then just change the sum cell's format to
"[h]:mm".

If you have these hours and minutes stored as (for example) "13" in col E
and "25" in col F to indicate 13:25 (13 hours and 25 minutes), then in col G
or wherever you'll need to convert these values to Excel-internal time; just
use =(E2+F2/60)/24. That gets you each row's time as expressed in fractions
of a day (which is how Excel does it); then format these cells to show hours
and minutes properly and sum the resulting column.

--- "bill" wrote:
During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

sum hours and minutes with Excel 2000
 

=IF(IF((OR(G11="",F11="")),0,IF((G11<F11),((G11-F11)*24)+24,(G11-F11)*24))=0,"",IF((OR(G11="",F11="")),0,IF((G11<F1 1),((G11-F11)*24)+24,(G11-F11)*24)))


On Thu, 15 Oct 2009 09:17:09 -0700, Bob Bridges
wrote:

Nothing could be simpler, Bill. If you have them recorded in Excel's notion
of time, ie if they're displayed using one of the time formats like "[h]:mm",
then you can simply sum them up. If the sum shows up looking like a date (eg
"January 13, 1900, 09:02"), then just change the sum cell's format to
"[h]:mm".

If you have these hours and minutes stored as (for example) "13" in col E
and "25" in col F to indicate 13:25 (13 hours and 25 minutes), then in col G
or wherever you'll need to convert these values to Excel-internal time; just
use =(E2+F2/60)/24. That gets you each row's time as expressed in fractions
of a day (which is how Excel does it); then format these cells to show hours
and minutes properly and sum the resulting column.

--- "bill" wrote:
During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

sum hours and minutes with Excel 2000
 

On Tue, 13 Oct 2009 18:47:01 -0700, bill
wrote:

During the year I kept a log of the hours and minutes spent daily on a
project. Now the tax man want me to post it in hours and minutes per week. I
have Excel 2000 and I would like to add up the total number of hours spent in
each week.

Bill


The Genie responds...

http://office.microsoft.com/en-us/te...CT101440991033