Solved: Summing time in a dynamic range?

[Simon Lloyd]

Hi all, i have a column which has many different times shown in this format 00:00:00, some may show 30 mins like 00:30:00 some may show 1Hr30Mins like 01:30:00 etc there are never any seconds (just the column is formatted as time!) all these times are amounts of hours and minutes worked on certain tasks, the tasks are paid by the hour.

My problem is when i SUM the column i don't get the correct amount of hours and minutes, i would like to find out the amount of wages for the range so if the range is called WorkedTime i want to multiply it by the amount of pay to find the expected wages, lets say the wages are ?8/hr

i can custom format the cell containing the formula to [h]mm:ss and then when i use =Sum(WorkedTime) i get the correct amount of hours and minutes but i am having trouble showing the actual wage!

Any ideas?

Regards,
Simon

[mdmackillop]

Can you post your workbook so we can see the formats you're using?

[Ken Puls]

Hi Simon,

Times in Excel are actually fractions of days. Just convert your numbers to a real number format and you'll see what I mean. Today, for example, is 39120, and noon on that would be 39120.5

Try taking your "hours" and multiplying it by 24 in your formula. It should probably do it for you.

HTH,

[mdmackillop]

Try formatting both your data and formula as [hh]:mm

[Simon Lloyd]

Ken i couldnt make it work....the workbook is attached as you will i have other plans and probably questions for the workbook when i get this over and done with!

Regards,
SImon

[mdmackillop]

As Ken says, hours are still part of a day, even in your formula. To get the total cost use
=SUM(WorkedTime)*8*24

[Ken Puls]

Hi Simon,

In I2:

=I1*8*24

Change the number format to $ or , or something (not time)

Is that what you mean?

[Simon Lloyd]

Malcom thanks for continuing with the responses yours came up with just an increased minute amount, Kens suggestion gave the correct amount (of course i have to leave the original formula cell intact and use one underneath!) once i formatted the cell as currency.

Thanks for the help............my next task now is to get the wages to work by month, i'm thinking of using an array formula for that something like
=IF(ROWS($C$2:CC2)<=COUNTIF('Client Time Sheet'!$B$2:$B$80,$E$2),INDEX(WorkedTime,SMALL(IF('Client Time Sheet'!$B$2:$B$80=$E$2,ROW('Client Time Sheet'!$B$2:$B$80)-ROW($A$2)+1),ROW(A1))),"") copied down it should show me all the matches for a date i set in E2............still needs playing around with!

Again thanks!

Simon

[Ken Puls]

Malcom thanks for continuing with the responses yours came up with just an increased minute amount, Kens suggestion gave the correct amount (of course i have to leave the original formula cell intact and use one underneath!) once i formatted the cell as currency.

Actually, no... You're right in that my solution as given needs to keep your cell there, but Malcolm's works. Format Malcolm's answer as a number (not time), and you'll see that it gives the same result.

:)

[Shazam]

=IF(ROWS($C$2:CC2)<=COUNTIF('Client Time Sheet'!$B$2:$B$80,$E$2),INDEX(WorkedTime,SMALL(IF('Client Time Sheet'!$B$2:$B$80=$E$2,ROW('Client Time Sheet'!$B$2:$B$80)-ROW($A$2)+1),ROW(A1))),"")

You could try this non-array approach.

Input formula in worksheet tab name "My Wage By Month" in cell F2 and copied down.

=IF(ROW()>ROW(F$1)+COUNTIF('Client Time Sheet'!$B$2:$B$80,$E$2),"",INDEX('Client Time Sheet'!$E$2:$E$80,MATCH($E$2,'Client Time Sheet'!$B$2:$B$80,0)+ROW()-ROW(F$2)))

[Bob Phillips]

Thanks for the help............my next task now is to get the wages to work by month, i'm thinking of using an array formula for that something like copied down it should show me all the matches for a date i set in E2............still needs playing around with it.

It's simpler than that is it not?

=SUMPRODUCT(--(MONTH($B$2:$B$1000)=ROW(A1)),$E$2:$E$1000)*24*8

for January, just copy down for Feb etc.

[Simon Lloyd]

Thanks for the replies, if you look at the sheet i intend to develop for summing hours and wages by month i have titles: Date | Hours | Average Wage, i am working on a formula to put in the hours column to calculate hours for a month the formula seemed to work (but of course summed all the hours so far because of SUM(WorkedTime), the formula , i thought relied on the value in A2 to be between 2 dates to produce a result however removing the value from A2 didnt change the result.



=IF(AND(OR($A2>="01/01/07",$A2<="31/01/07")),SUM(WorkedTime),0)

My ideal formula would be: IF Ax contained a month name Like "January" THEN SUM WorkedTime (column E) FOR all ROWS that fall in that month, somethin like
=IF(A2=MONTH,SUM(ALL ROWS MATCHING MONTH ON CLIENT SHEET),0) although i wish it was this simple!

Regards,
SImon

[Simon Lloyd]

Ok i removed the OR statement and it now makes a difference if there is a value in A2 however the value has to be text !

Still doesnt work of course as i want

Regards,
Simon

[Bob Phillips]

.

[Simon Lloyd]

Bob not checked out your attachment yet but i was fiddling with this


=IF(AND($A2>="01 January 2007",$A2<="31 January 2007"),SUM(ROWS({"01 January 2007","31 January 2007"})),0)
which if the date fell between those in the formula it returned a value of 1 if it fell outside of the dates it returned a value of 0, i am assuming its because it is only checking for the occurrence of the date and counting the amount of times.....although i'm probably way of mark!

Regards,
Simon

[Simon Lloyd]

Just checked it out ...nice!, i tried to look for "dd/mmm/yyyy" never thought of looking for "mmmm" and also didnt think of using the TEXT function to look for it!

Thanks for the solution!, saves me hours 'n' hours and of course loads of questions!

This workbook is for my partner i intend to automate as much as possible, add a userform for client entry and finding clients and details, and more columns for added information, right now she has a bag full of pieces of crumpled paper! - not good!

Regards,
SImon

[Bob Phillips]

You need to learn the joys of SUMPRODUCT.

SUMIF is great for doing conditional sums such as this, but it struggles with partial comparisons, whereas SUMPRODUCT gobbles these up.

[Simon Lloyd]

I had a look at SUMPRODUCT() but then i got a bit put off because of the need for arrays in this and haven't got my head on straight for working the relationship between what i excpected from my data and translating that to the array!

Remember Bob...Bungalows.....Skyscrapers are a way off!

Lol

Kind Regards,
Simon