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

It will substitute the periods in each of the cells with a semicolon - which is needed for time conversion in Excel. It will then take the first value and take it's negative value * 24, then the second number's positive value * 24, and so on, then sum those values.

What version of Excel do you have? If 2002 or later, use the formula auditer to view each step of the formula.

The SUBSTITUTE() function takes all the commas and periods that might be in the times, and replaces them with colons, so that Excel will recognize them as times. Then these values are multiplied by the array {-1,1,-1,1,-1,1} which means that you'd get -5:30 , 8:32 , -9:02, etc. Then all these values are added, effectively adding the differences between the start times, and end times. So you'd get:
8:32-5:30 + 10:00-9:02, etc.

That'll get you the total number of days, which are then multiplied by 24 to get hours. Make sense?

EDIT: Hi Zack!

Well at home I'm using 2003 But at work I'm using windows 98. Can you explain more carefully again what this mean? -5:30 , 8:32 , -9:02, etc. Then all these values are added, effectively adding the differences between the start times, and end times. So you'd get:
8:32-5:30 + 10:00-9:02, etc. Also how can I do this on the fx fucntion on excel?

Thanks!

The substitute just converts the values in your cells to times, e.g. 05.30 becomes 05:30

then the "{-1,1,-1,1,-1,1}" means that your cell values (now times) in C3 to H3 are multiplied by those values, this effectively gives you

08:32 - 05:30 +
10:00 - 09:02 +
14:00 - 10:20

so that's your total time worked (in days)

multiply by 24 to give you the time in hours

edit - but I see that's already been explained above!

If you're looking to do this with a single function, you're out of luck. The array formula you were given is probably about as good as you're going to get, in a one-liner.

vane0326 said:

.. Also how can I do this on the fx fucntion on excel?

Click to expand...

You don't; if I understand you correctly. That is the function wizard, which will allow you to enter various types of syntax's for formulas. So it would not apply here. You could, however, put your cursor in the formula bar (left click once) and press F9 to see the calcculations performed in the formula bar. Take note that if you confirm this entry, the values will be kept as they have been calculated and any references will be lost. So always confirm this type of behavior with the Esc key when finished.

(Heya TG!! How ya been?!)

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

Well what does this means in the formula ,".",":") and text

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

Anyone has a step to using that formula in the sum wizard?

Well I need that formula to get the time 7.67

Thanks!

If you look at the following rewrite...

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

you see that two arrays are multiplied. Fx cannot supply them by just range selection. You need to understand the way it's constructed.

You have ib C3:H3...

5.3, 8.32, 9.02, 10, 10.2, 14

which stand for or represent:

5:30, 8:32, 9:02, 10:00, 10:20, 14:00

To obtain the latter series...

1] Apply

TEXT(C3:H3,"0.00")

in order obtain numbers in 0.00 format which makes them look like time values...

{"5.30","8.32","9.02","10.00","10.20","14.00"}

with the exception of dots. Using SUBSTITUTE, one can replace dot with colon...

SUBSTITUTE({"5.30","8.32","9.02","10.00","10.20","14.00"},".",":")

{"5:30","8:32","9:02","10:00","10:20","14:00"}

This text-formatted time values will all become real, numeric values if multiplied with a number or set of numbers. In the array formula, this conversion into numbers occur by *. In the SumProduct version by the preceeding double negation. Thus:

--{"5:30","8:32","9:02","10:00","10:20","14:00"}

leads to:

{0.229166666666667,0.355555555555556,0.376388888888889,0.416666666666667,0.430555555555556,0.583333333333333}

If this array values can be multiplied with an equally sized identity vector (vector consisting of 1's) of which the sign (- or +) of each item appropriately selected like in this case

{-1,1,-1,1,-1,1}

one obtains after multiplication (by * in the Sum version and by , in the SumProduct version)...

{-0.229166666666667,0.355555555555556,-0.376388888888889,0.416666666666667,-0.430555555555556,0.583333333333333}

When summed, one gets effectively the required subtractions and additions and obtains the desired result.