Convert to hours, minutes and seconds

[jkitterm]

I have a spreadsheet with the time someone has been in an application expressed as 128:23:44 or 128 hours, 23 minutes and 44 seconds
Unfortunately Excel is reading it as a date and time 1/5/1900 8:23:44 AM
I can't seem to find the right format so Excel understands it is 128 hours, 23 minutes and 44 seconds and not a date and time.
Can someone help me with this?
Ultimately I would like to be able to accurately separate out the hours, minutes and second in separate columns which I can currently do but only if the total time is less than 24 hours. (=HOUR(A1)

 

[Fluff]

To format the cell like that use [h]:mm:ss

 

[Fluff]

To get the total number of hours you could use

=HOUR(A1)+INT(A1)*24

 

[jkitterm]

To format the cell like that use [h]:mm:ss

that gives me the same result 1/5/1900 8:23:44 AM
I am trying to have excel understand that the the 128 is 128 hours not a date

 

[Fluff]

In that case you did not use the cell format I suggested.

 

[jkitterm]

=HOUR(A1)+INT(A1)*24

This produces an error

In that case you did not use the cell format I suggested.

I did.

Perhaps this will help explain further

Copy of time conversion example.xlsx
	A	B	C	D	E	F	G
1	Time in Application	Converted time	Logged on time hours	Logged on time minutes	Logged on time seconds	Logged on time total seconds	Logged on time seconds
2	128:23:44	8:23:44	8	23	44	30224	462224
3	18:12:50	18:12:50	18	12	50	65570	65570
4
5	Row 2 above should be
6	Time in Application	Converted time	Logged on time hours	Logged on time minutes	Logged on time seconds	Logged on time total seconds	Logged on time seconds
7	128:23:44	128:23:44	128	23	44	462224	462224
8	128:23:44	#VALUE!
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=A2
C2:C3	C2	=HOUR(B2)
D7,D2:D3	D2	=MINUTE(B2)
E7,E2:E3	E2	=SECOND(B2)
F7,F2:F3	F2	=SUM(((C2*60)*60)+(D2*60))+E2
G7,G2:G3	G2	=SUM(A2*86400)
B8	B8	=HOUR(A1)+INT(A1)*24

 

[Fluff]

You need to change the A1 in the formula to point to the cell with the date/time.

 

[jtakw]

Hi,

May be this to make a bit more clear for you, cleaned up some of your formulas.
I don't understand the need for Column B, since it's just =A

Book3.xlsx
	A	B	C	D	E	F	G
1	Time in Application	Converted time	Logged on time hours	Logged on time minutes	Logged on time seconds	Logged on time total seconds	Logged on time seconds
2	128:23:44	128:23:44	128	23	44	462224	462224
3	18:12:50	18:12:50	18	12	50	65570	65570
4		^formatted [h]:mm:ss	^formatted General	^formatted General	^formatted General	^formatted General	^formatted General
Sheet1055

Cell Formulas
Range	Formula
B2:B3	B2	=A2
C2:C3	C2	=HOUR(A2)+INT(A2)*24
D2:D3	D2	=MINUTE(A2)
E2:E3	E2	=SECOND(A2)
F2:F3	F2	=SUM(C2*3600,D2*60,E2)
G2:G3	G2	=A2*86400

 

[Dave Patton]

N.B. C7 is custom formatted [h]

For this example A8 = A7

Time.xlsm
	A	B	C	D	E	F	G
6	Time in Application	Converted time	Logged on time hours	Logged on time minutes	Logged on time seconds	Logged on time total seconds	Logged on time seconds
7	128:23:44		128	23	44
8	128:23:44		128	23	44		462224
9
6b

Cell Formulas
Range	Formula
C7	C7	=A7
D7:D8	D7	=MINUTE(A7)
E7:E8	E7	=SECOND(A7)
C8	C8	=INT(A8)*24+HOUR(A8-INT(A8))
A8	A8	=A7
G8	G8	=C8*60*60+D8*60+E8

 

[jkitterm]

Hi,

May be this to make a bit more clear for you, cleaned up some of your formulas.
I don't understand the need for Column B, since it's just =A

Book3.xlsx
	A	B	C	D	E	F	G
1	Time in Application	Converted time	Logged on time hours	Logged on time minutes	Logged on time seconds	Logged on time total seconds	Logged on time seconds
2	128:23:44	128:23:44	128	23	44	462224	462224
3	18:12:50	18:12:50	18	12	50	65570	65570
4		^formatted [h]:mm:ss	^formatted General	^formatted General	^formatted General	^formatted General	^formatted General
Sheet1055

Cell Formulas
Range	Formula
B2:B3	B2	=A2
C2:C3	C2	=HOUR(A2)+INT(A2)*24
D2:D3	D2	=MINUTE(A2)
E2:E3	E2	=SECOND(A2)
F2:F3	F2	=SUM(C2*3600,D2*60,E2)
G2:G3	G2	=A2*86400

Thank You! Makes sense now....