Convert half hour to hour using military time

[tblackwell]

Newb here so bear with me if the answer is obvious!

Situation: Trying to convert field of half hour with that contain no ":" to full hours using military time. It's relatively easy for half hours that contain four characters.
Hypothetical Example: =left(text,[num_char]) for "1000" returns the desired result of "10"
Problem: =left(text,[num_char]) for "930" returns "93" when my desired result is "9"
Current formula where I get stuck: not sure what formula i need beyond: =left(text,[num_char])
System: Windows 10 Pro

Appreciate any help you can provide on the right formula for column M!

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	half hour breakout		desired result		good formula		problem children		desired result		bad formula result		good formula
2	0		0		0		30		0		30		?
3	1000		10		10		930		9		93		?
4	1030		10		10		930		9		93		?
5	1100		11		11		30		0		30		?
6	1130		11		11		530		5		53		?
7	1200		12		12		800		8		80		?
8	1230		12		12		930		9		93		?
9	1300		13		13		30		0		30		?
10	1330		13		13		30		0		30		?
11	1400		14		14		930		9		93		?
12	1430		14		14		30		0		30		?
13	1500		15		15		630		6		63		?
14	1530		15		15		700		7		70		?
15	1600		16		16		730		7		73		?
16	1630		16		16		800		8		80		?
17	1700		17		17		830		8		83		?
18	1730		17		17		900		9		90		?
19	1800		18		18		930		9		93		?
20	1830		18		18		700		7		70		?
21	1900		19		19		730		7		73		?
22	1930		19		19		800		8		80		?
23	2000		20		20		830		8		83		?
24	2030		20		20		900		9		90		?
25	2100		21		21		930		9		93		?
26	2130		21		21		730		7		73		?
27	2200		22		22		800		8		80		?
28	2230		22		22		830		8		83		?
29	2300		23		23		900		9		90		?
30	2330		23		23		930		9		93		?
31							30		0		30		?
32							900		9		90		?
33							30		0		30		?
34							630		6		63		?
35							700		7		70		?

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =LEFT(A2,2) E3 =LEFT(A3,2) E4 =LEFT(A4,2) E5 =LEFT(A5,2) E6 =LEFT(A6,2) E7 =LEFT(A7,2) E8 =LEFT(A8,2) E9 =LEFT(A9,2) E10 =LEFT(A10,2) E11 =LEFT(A11,2) E12 =LEFT(A12,2) E13 =LEFT(A13,2) E14 =LEFT(A14,2) E15 =LEFT(A15,2) E16 =LEFT(A16,2) E17 =LEFT(A17,2) E18 =LEFT(A18,2) E19 =LEFT(A19,2) E20 =LEFT(A20,2) E21 =LEFT(A21,2) E22 =LEFT(A22,2) E23 =LEFT(A23,2) E24 =LEFT(A24,2) E25 =LEFT(A25,2) E26 =LEFT(A26,2) E27 =LEFT(A27,2) E28 =LEFT(A28,2) E29 =LEFT(A29,2) E30 =LEFT(A30,2) K2 =LEFT(G2,2) K3 =LEFT(G3,2) K4 =LEFT(G4,2) K5 =LEFT(G5,2) K6 =LEFT(G6,2) K7 =LEFT(G7,2) K8 =LEFT(G8,2) K9 =LEFT(G9,2) K10 =LEFT(G10,2) K11 =LEFT(G11,2) K12 =LEFT(G12,2) K13 =LEFT(G13,2) K14 =LEFT(G14,2) K15 =LEFT(G15,2) K16 =LEFT(G16,2) K17 =LEFT(G17,2) K18 =LEFT(G18,2) K19 =LEFT(G19,2) K20 =LEFT(G20,2) K21 =LEFT(G21,2) K22 =LEFT(G22,2) K23 =LEFT(G23,2) K24 =LEFT(G24,2) K25 =LEFT(G25,2) K26 =LEFT(G26,2) K27 =LEFT(G27,2) K28 =LEFT(G28,2) K29 =LEFT(G29,2) K30 =LEFT(G30,2) K31 =LEFT(G31,2) K32 =LEFT(G32,2) K33 =LEFT(G33,2) K34 =LEFT(G34,2) K35 =LEFT(G35,2) <tbody> </tbody>

<tbody>
</tbody>

 

[Fluff]

How about
=IFERROR(LEFT(G2,LEN(G2)-2)*1,0)

 

[Rick Rothstein]

How about
=IFERROR(LEFT(G2,LEN(G2)-2)*1,0)

I think this formula would also work...

=INT(G2/100)

 

[SpillerBD]

I would think storing time as a time values would be the first thing to look at.
Format cells as "hmm;@" or "hhmm;@"
Then you only need =HOUR(A1)

Of course this may be dependent on where these values are coming from.

 

[tblackwell]

Geez I didn't even know about the INT function. It seems so obvious now. Thanks Rick Rothstein! And thanks Fluff, because your solution worked too. Need to start integrating LEN more.

QQ: When a problem is solved is there a feature in the forum to note that? Or do I just give thanks and likes?

 

[Fluff]

Glad we could help & thanks for the feedback.

We don't allow threads to be marked as solved, If you wish to click Thanks/Likes you are welcome to do so, but it's not mandatory.