=SUM(SUBSTITUTE(TEXT(C3:H3,"0.00"),".",&

[vane0326]

I've been trying to figuer this formula all week and I just dont know how he got it to work. Is there anyone that can give me a explanation how to get this formula by using the fx function in excel and step by step of what array in formula does: =SUM(SUBSTITUTE(TEXT(C3:H3,"0.00"),".",":")*{-1,1,-1,1,-1,1})*24

Employee Time Sheet.xls
	B	C	D	E	F	G	H	I
2		Start Time	Lunch Start	Lunch End	Break Start	Break End	End Time	Total
3	Employee	5.30	8.32	9.02	10.00	10.20	14.00	7.67
Sheet1

 

[Zack Barresse]

You must use the TEXT function so the numbers will all show up with the same amount of fixed decimal places, else failurre of time conversion will follow. the ,".",":") part says replace all "." with ":". So that is where the time conversion comes into play.

And as previously stated, you cannot get the results you are looking for from the Sum wizard. You need to use F9 in the formula bar or use the formula auditor in 2002 or above. One other possibility would be Aaron Blood's Explode: http://www.xl-logic.com/pages/explode.html.

 

[Aladin Akyurek]

If there's any way you can have your times shown as true times, e.g. 05:30, 09:02 etc. then you wouldn't need such an elaborate formula, you'd just need

=(H3-C3-E3+D3-G3+F3)*24 formatted as number

to give you the total hours worked

True. However, it's not unitelligible. All that is required a working knowledge of array/vector multiplication and summing. Thus, rewriting your suggestion (absolutely no need to do so though), one gets:

=SUMPRODUCT(C3:H3,{-1,1,-1,1,-1,1})

by

-C3, D3, -E3, F3, -G3, H3

for

=SUM(-C3, D3, -E3, F3, -G3, H3) is equal to =(H3-C3-E3+D3-G3+F3)

 

[vane0326]

That formula goes work and you'll get your answer 7.80 but to convert it to time value it wont work. meaning you wont get 7.67 using this formula!
=SUMPRODUCT(C3:H3,{-1,1,-1,1,-1,1})

But how ever the formula you posted earlier does work and you will get the 7.67

=SUMPRODUCT(--SUBSTITUTE(TEXT(C3:H3,"0.00"),".",":"),{-1,1,-1,1,-1,1})*24

Thanks for the rewrite!

 

[Dutchy]

Aladin,

I only suggested =(H3-C3-E3+D3-G3+F3)*24

because back in Vane's original thread he'd asked why

=(H3-C3-E3+D3-G3+F3) was giving the wrong answer, namely 7.80.

The answer was, of course, that the values were numbers not times so rather than opting for a wholly different approach I wondered whether he could simply have converted the decimals to times and adjusted the formula very slightly.

I agree that the array formulas you suggest are not as challenging as they might first appear, I'm even starting to get the hang of them myself!

 

[Aladin Akyurek]

That formula goes work and you'll get your answer 7.80 but to convert it to time value it wont work. meaning you wont get 7.67 using this formula!
=SUMPRODUCT(C3:H3,{-1,1,-1,1,-1,1})

You missed the point...

This formula will work if C3:H3 houses true time values. But then such a formula is not needed. That's what Dutchy was pointing out. I picked that up to explain how/why the Sum formula you were given.

Thus, in your case, the conversion of the values in C3:H3 to true time values are required. Hence:

{=SUM(--SUBSTITUTE(TEXT(C3:H3,"0.00"),".",":"),{-1,1,-1,1,-1,1})*24}

or faster

=SUMPRODUCT(--SUBSTITUTE(TEXT(C3:H3,"0.00"),".",":"),{-1,1,-1,1,-1,1})*24

which just needs enter.