How to Convert a number to a Time value?

[vane0326]

In Collumn (J3) I want to convert the number in I3 to a time value but it gives me a #VALUE! error. Why?

Any Ideas?

Thanks!

PERSONAL.XLS
	B	C	D	E	F	G	H	I	J
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	#VALUE!
4
5
Sheet1

 

[Mark W.]

What's 7.8 supposed to be? Didn't they work 7 hrs 40 min (="14:00"-"5:30"-"0:50")?

 

[vane0326]

Thanks it Works!

PERSONAL.XLS
	B	C	D	E	F	G	H	I	J
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	7:48
4
Sheet1

 

[Mark W.]

What's 7.8?

 

[vane0326]

I format the cells by 2 decimals in I3. Thats why it came out 7.80 I went back and changed the format by zero decimals.

Thanks!

 

[Mark W.]

What I'm trying to suggest is that....

=IF(H3< C3,1+H3-C3,H3-C3)-IF(E3< D3,1+E3-D3,E3-D3)-IF(G3< F3,1+G3-F3,G3-F3)

...isn't correct. The correct answer is 7.66666

 

[vane0326]

I think you are right How did you get that 7.666 What formula did you use?

Thanks!

 

[Mark W.]

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, {}, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on this topic see the Excel help topic for "Create an array formula".

Book1
	B	C	D	E	F	G	H	I	J
1
2
3		5.3	8.32	9.02	10	10.2	14	7.666666667
4
5
Sheet1

 

[vane0326]

Its works perfectly! How did you create that formula?

Thanks!

 

[vane0326]

Everytihng was discussed in this thread!

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