Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count hours per month
Hi All
I have a spreadsheet where i need to count the number of hours and minutes spent working on products per month In cell range 'H' i have the time spent working i.e. 00:40 (40 minutes) In cell range 'P' i have the dates i.e. 15/09/07 How do i calculate how many hours and minutes were spent working on products during Jan,Feb,Apr etc? I have tried this formula, but it only works for some months (works in January, but not in September) =SUMPRODUCT(--(TEXT('With costs'!P2:P5000,"mm yyyy")="08 2007"),--('With costs'!H2:H5000)) Many thanks Derek |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count hours per month
You could try this:
put the numbers 1 to 12 in cells X1:X12, then in Z1 enter this formula: =SUMPRODUCT((MONTH('With costs'!P$2:P$5000)=X1)*('With costs'!H$2:H $5000)) and copy the formula down into Z2:Z12. If you want to distinguish between different years then put 2007 in Y1:Y12 and amend the formula to: =SUMPRODUCT((MONTH('With costs'!P$2:P$5000)=X1)*(YEAR('With costs'!P $2:P$5000)=Y1)*('With costs'!H$2:H$5000)) and copy down. Hope this helps. Pete On Sep 19, 10:08 am, Derek wrote: Hi All I have a spreadsheet where i need to count the number of hours and minutes spent working on products per month In cell range 'H' i have the time spent working i.e. 00:40 (40 minutes) In cell range 'P' i have the dates i.e. 15/09/07 How do i calculate how many hours and minutes were spent working on products during Jan,Feb,Apr etc? I have tried this formula, but it only works for some months (works in January, but not in September) =SUMPRODUCT(--(TEXT('With costs'!P2:P5000,"mm yyyy")="08 2007"),--('With costs'!H2:H5000)) Many thanks Derek |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count hours per month
Thank you Pete, ill try that
"Pete_UK" wrote: You could try this: put the numbers 1 to 12 in cells X1:X12, then in Z1 enter this formula: =SUMPRODUCT((MONTH('With costs'!P$2:P$5000)=X1)*('With costs'!H$2:H $5000)) and copy the formula down into Z2:Z12. If you want to distinguish between different years then put 2007 in Y1:Y12 and amend the formula to: =SUMPRODUCT((MONTH('With costs'!P$2:P$5000)=X1)*(YEAR('With costs'!P $2:P$5000)=Y1)*('With costs'!H$2:H$5000)) and copy down. Hope this helps. Pete On Sep 19, 10:08 am, Derek wrote: Hi All I have a spreadsheet where i need to count the number of hours and minutes spent working on products per month In cell range 'H' i have the time spent working i.e. 00:40 (40 minutes) In cell range 'P' i have the dates i.e. 15/09/07 How do i calculate how many hours and minutes were spent working on products during Jan,Feb,Apr etc? I have tried this formula, but it only works for some months (works in January, but not in September) =SUMPRODUCT(--(TEXT('With costs'!P2:P5000,"mm yyyy")="08 2007"),--('With costs'!H2:H5000)) Many thanks Derek |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count hours per month
You're welcome, Derek - hope it works for you.
Pete On Sep 19, 10:44 am, Derek wrote: Thank you Pete, ill try that "Pete_UK" wrote: You could try this: put the numbers 1 to 12 in cells X1:X12, then in Z1 enter this formula: =SUMPRODUCT((MONTH('With costs'!P$2:P$5000)=X1)*('With costs'!H$2:H $5000)) and copy the formula down into Z2:Z12. If you want to distinguish between different years then put 2007 in Y1:Y12 and amend the formula to: =SUMPRODUCT((MONTH('With costs'!P$2:P$5000)=X1)*(YEAR('With costs'!P $2:P$5000)=Y1)*('With costs'!H$2:H$5000)) and copy down. Hope this helps. Pete On Sep 19, 10:08 am, Derek wrote: Hi All I have a spreadsheet where i need to count the number of hours and minutes spent working on products per month In cell range 'H' i have the time spent working i.e. 00:40 (40 minutes) In cell range 'P' i have the dates i.e. 15/09/07 How do i calculate how many hours and minutes were spent working on products during Jan,Feb,Apr etc? I have tried this formula, but it only works for some months (works in January, but not in September) =SUMPRODUCT(--(TEXT('With costs'!P2:P5000,"mm yyyy")="08 2007"),--('With costs'!H2:H5000)) Many thanks Derek- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of Hours Per Month | New Users to Excel | |||
How can I count hours & minutes??!! | Excel Discussion (Misc queries) | |||
count TIME that is GREATER than 24 hours | Excel Worksheet Functions | |||
Add up the hours for each month | Excel Worksheet Functions |