Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum of Hours Per Month
I have been given a data set which contains work hours and dates in the form
of mm/dd/yy. I am trying to sum all the hours worked in each month. A co-worker suggested I use sumproduct but I'm still not able to achieve the process. Additionally, the data crosses over years, so I am not quite sure how to create a fomula that takes the year into account. Any help would be great. Thanks much. -- Zachary Baker |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum of Hours Per Month
Using a pivot table grouped by months and years would be the most
straightforward. "Zachary Baker" <Zachary wrote in message ... I have been given a data set which contains work hours and dates in the form of mm/dd/yy. I am trying to sum all the hours worked in each month. A co-worker suggested I use sumproduct but I'm still not able to achieve the process. Additionally, the data crosses over years, so I am not quite sure how to create a fomula that takes the year into account. Any help would be great. Thanks much. -- Zachary Baker |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum of Hours Per Month
Zachary,
For example, to get the values for January of this year, with dates in column A and hours in B: =SUMPRODUCT((A1:A2000=DATE(2006,1,1))*(A1:A2000<D ATE(2006,2,1))*B1:B2000) Easier would be a pivot table, with the data grouped based on months. HTH, Bernie MS Excel MVP "Zachary Baker" <Zachary wrote in message ... I have been given a data set which contains work hours and dates in the form of mm/dd/yy. I am trying to sum all the hours worked in each month. A co-worker suggested I use sumproduct but I'm still not able to achieve the process. Additionally, the data crosses over years, so I am not quite sure how to create a fomula that takes the year into account. Any help would be great. Thanks much. -- Zachary Baker |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sum of Hours Per Month
I'll suppose you have a date in column A and hours in column B.
To calculate the hours worked in May '06: =sumif(a:a,"="&date(2006,5,1),b:b)-sumif(a:a,"="&date(2006,6,1),b:b) The first part adds all hours worked on/after May 1 '06; the second part subtracts out all hours worked on/after June 1 '06, leaving only those worked during the month of May '06. "Zachary Baker" wrote: I have been given a data set which contains work hours and dates in the form of mm/dd/yy. I am trying to sum all the hours worked in each month. A co-worker suggested I use sumproduct but I'm still not able to achieve the process. Additionally, the data crosses over years, so I am not quite sure how to create a fomula that takes the year into account. Any help would be great. Thanks much. -- Zachary Baker |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to convert hours and minutes to hours and tenths. | Excel Discussion (Misc queries) | |||
Can this adjusted GPA formual be accomplised? | Excel Discussion (Misc queries) | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
Subtracting paid hours from unpaid hours | Excel Worksheet Functions | |||
Add up the hours for each month | Excel Worksheet Functions |