Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2019, 03:27 PM
taholmes160 taholmes160 is offline How do I format a cell to allow for entry and display of proper 24hour notation Windows 10 How do I format a cell to allow for entry and display of proper 24hour notation Office 2016
Novice
How do I format a cell to allow for entry and display of proper 24hour notation
 
Join Date: Jan 2019
Location: Lester Ohio
Posts: 7
taholmes160 is on a distinguished road
Unhappy 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
Reply With Quote
  #2  
Old 01-21-2019, 12:33 AM
ArviLaanemets ArviLaanemets is offline How do I format a cell to allow for entry and display of proper 24hour notation Windows 8 How do I format a cell to allow for entry and display of proper 24hour notation Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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)
and copy the formula down for entire table.

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.)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I format a cell to allow for entry and display of proper 24hour notation How to have one cell show a value based on the entry of another cell? Marvinapplegate1964 Excel 1 05-04-2016 01:49 PM
How do I format a cell to allow for entry and display of proper 24hour notation 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
How do I format a cell to allow for entry and display of proper 24hour notation Autocorrect in Word not selecting proper entry from list Llewella Word 4 05-12-2012 02:22 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft