Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing minutes from hours:minutes in Excel | Excel Worksheet Functions | |||
Converting total minutes into hours and minutes in Excel | Excel Worksheet Functions | |||
How do I convert decimal values to hours & minutes in Excel 2000? | Excel Discussion (Misc queries) | |||
converting Days Hours & minutes into just minutes in excel | Excel Discussion (Misc queries) | |||
add hours & minutes to other hours & minutes to receive total hou. | Excel Worksheet Functions |