How to get this time value correct?

[vane0326]

Why in cell I3 the result is 7.80 It should read 7.66 What I'm doing wrong. How can I modified my formula so my result could be 7.66?

Thanks!

PERSONAL.XLS
	B	C	D	E	F	G	H	I
1
2		Start Time	Lunch Start	Lunch End	Break Start	Break End	End Time	Total
3	John	5.30	8.32	9.02	10.00	10.20	14.00	7.80
Sheet1

 

[vane0326]

Thanks Mark W!

He gave me this formula.
But how did he do it?

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

 

[vane0326]

If you dont like arrays you could use this formula.

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

 

[rs2k]

Am I reading this wrong, shouldn't the answer be 7:40?
If that is time worked (breaks deducted)
Cells formatted [h]:mm
Cell I3 =(H3-C3)-(E3-D3)-(G3-F3)

Is that not what you are asking?
Colin

 

[Norie]

Instead of entering the times as 5.30 etc try 5:30.

Then Excel will actually treat them as times.

 

[Dutchy]

...already been some discussion of this in another thread

http://www.mrexcel.com/board2/viewtopic.php?t=132938&highlight=

 

[vane0326]

This is my first original post how to convert time to time values.


http://www.mrexcel.com/board2/viewtopic.php?t=132102&highlight=