tblackwell
New Member
- Joined
- Oct 24, 2018
- Messages
- 28
- Office Version
- 365
- Platform
- Windows
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!
<tbody>
</tbody>
<tbody>
</tbody>
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
<tbody> </tbody> |
<tbody>
</tbody>