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
|