|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I format a cell to allow for entry and display of proper 24hour notation
HI Folks:
Im about to pull my hair out here Im running Microsoft Excel for Office 365 MSO (16.0.11126.20234) 32-bit I am working on a work sheet where I will need to enter a lot of times, but do not have to do any math to them -- its a schedule listing. The appropriate format for the times are as follows 6:15am = 0615 12:01pm = 1201 3:47pm = 1547 10:21pm = 2221 Midnight = 0000 This is what I know as proper military format, I want to be able to enter it in military format and have it display in military format -- including all leading zeros (I SPECIFICALLY do NOT want to type a colon ( or AM/PM) -- those are very hard to do on the 10key pad I have tried and tried to re-format the cells -- as far as I can tell from reading the online resources the proper format should be custom with the Type hhmm, however formatting as hhmm and typing 0600 returns a value of 0000 in the cell and 1/25/1900 00 in the formula bar. This shouldnt be so difficult, but for some reason, I am having a real difficult time with it Where am I going wrong Tim |
#2
|
|||
|
|||
The easiest way is to enter the time string (in Military Time format) as text, and calculate the time value in another column (formatted as Military Time format too, or in any other Time format you prefer).
E.g. Column A has header MTSring in A1, is formatted as text, and has e.g. the value 0615 entered into cell A2. Jou have another column e.g. B with header TimeValue in B2, formatted with some Time format of your choice. Into B2, enter the formula like Code:
=TIME(MID($A2,1,2),MID($A2,3,2);0) User enters the time string into column MTString. In all calculations, the time is read from column TimeValue. To avoid the confusion, you can even hide the column TimeValue from user. (Of-course you also can omit the column TimeValue at all, and simply calculate the time value directly from Military Time string in column MTString in your formulas.) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to have one cell show a value based on the entry of another cell? | Marvinapplegate1964 | Excel | 1 | 05-04-2016 01:49 PM |
Change houre notation when wrong notation is entered | dlquinte | Excel | 1 | 04-12-2016 03:35 AM |
Proper Text Format | sufian,naeem | Excel | 1 | 05-05-2014 05:59 AM |
How to format cell in order to display *both* time and date | SamyCode | Excel | 3 | 01-22-2013 03:30 PM |
Autocorrect in Word not selecting proper entry from list | Llewella | Word | 4 | 05-12-2012 02:22 PM |