A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Format cell as military time



 
 
Thread Tools Display Modes
  #1  
Old July 17th 06, 08:28 PM posted to microsoft.public.excel.misc
Mike
external usenet poster
 
Posts: 3,102
Default Format cell as military time

I want to type in 0700 in a cell and press <tab> to go to the next cell and
it will auto format it like 07:00 (military time). ex: type in 1400 and it
display as 14:00, etc. In other words, I want to be lazy and not have to type
the colon. How do I need to format the cell?
Thanks... Mike

Ads
  #2  
Old July 17th 06, 08:46 PM posted to microsoft.public.excel.misc
JE McGimpsey
external usenet poster
 
Posts: 4,624
Default Format cell as military time

Note that US/NATO military times do not use colons.

To do what you're asking, see

http://cpearson.com/excel/datetimeentry.htm



In article >,
Mike > wrote:

> I want to type in 0700 in a cell and press <tab> to go to the next cell and
> it will auto format it like 07:00 (military time). ex: type in 1400 and it
> display as 14:00, etc. In other words, I want to be lazy and not have to type
> the colon. How do I need to format the cell?
> Thanks... Mike

  #3  
Old July 17th 06, 09:16 PM posted to microsoft.public.excel.misc
Mike
external usenet poster
 
Posts: 3,102
Default Format cell as military time

JE,

I appreciate the info but I am not familiar with how to set this up. Where
do I need to paste that code in my worksheet to make it work?

Thanks...
Mike

"JE McGimpsey" wrote:

> Note that US/NATO military times do not use colons.
>
> To do what you're asking, see
>
> http://cpearson.com/excel/datetimeentry.htm
>
>
>
> In article >,
> Mike > wrote:
>
> > I want to type in 0700 in a cell and press <tab> to go to the next cell and
> > it will auto format it like 07:00 (military time). ex: type in 1400 and it
> > display as 14:00, etc. In other words, I want to be lazy and not have to type
> > the colon. How do I need to format the cell?
> > Thanks... Mike

>

  #4  
Old July 17th 06, 09:24 PM posted to microsoft.public.excel.misc
Bob Phillips
external usenet poster
 
Posts: 10,594
Default Format cell as military time

Maybe this will help

http://www.cpearson.com/excel/DateTimeEntry.htm

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mike" > wrote in message
...
> I want to type in 0700 in a cell and press <tab> to go to the next cell

and
> it will auto format it like 07:00 (military time). ex: type in 1400 and it
> display as 14:00, etc. In other words, I want to be lazy and not have to

type
> the colon. How do I need to format the cell?
> Thanks... Mike
>



  #5  
Old July 17th 06, 09:25 PM posted to microsoft.public.excel.misc
JE McGimpsey
external usenet poster
 
Posts: 4,624
Default Format cell as military time

See

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In article >,
Mike > wrote:

> I appreciate the info but I am not familiar with how to set this up. Where
> do I need to paste that code in my worksheet to make it work?

  #6  
Old July 17th 06, 09:27 PM posted to microsoft.public.excel.misc
shadestreet
external usenet poster
 
Posts: 1
Default Format cell as military time


If your goal is to be lazy, why not just format the cells to military
time and then type in "1 pm", "6 am", "11 pm", etc. The output would
be: 13:00, 6:00, 23:00.

Personally typing 1 pm is faster than typing 1300 since your fingers
toggle between numbers and letters.

If you want to have 6:00 appear at 06:00 then instead of formatting to
time, go to custom and enter hh:mm into the box.


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=562200

  #7  
Old July 17th 06, 10:23 PM posted to microsoft.public.excel.misc
RagDyeR
external usenet poster
 
Posts: 3,574
Default Format cell as military time

This works for smart as well as lazy people:

http://tinyurl.com/r3ngw

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike" > wrote in message
...
>I want to type in 0700 in a cell and press <tab> to go to the next cell and
> it will auto format it like 07:00 (military time). ex: type in 1400 and it
> display as 14:00, etc. In other words, I want to be lazy and not have to
> type
> the colon. How do I need to format the cell?
> Thanks... Mike
>


  #8  
Old July 17th 06, 10:32 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,911
Default Format cell as military time

Copy the second lot of code from Chip's site.

Right-click on your sheet tab and "View Code"

Paste into that sheet module.


Gord Dibben MS Excel MVP


On Mon, 17 Jul 2006 13:16:01 -0700, Mike > wrote:

>JE,
>
>I appreciate the info but I am not familiar with how to set this up. Where
>do I need to paste that code in my worksheet to make it work?
>
>Thanks...
>Mike
>
>"JE McGimpsey" wrote:
>
>> Note that US/NATO military times do not use colons.
>>
>> To do what you're asking, see
>>
>> http://cpearson.com/excel/datetimeentry.htm
>>
>>
>>
>> In article >,
>> Mike > wrote:
>>
>> > I want to type in 0700 in a cell and press <tab> to go to the next cell and
>> > it will auto format it like 07:00 (military time). ex: type in 1400 and it
>> > display as 14:00, etc. In other words, I want to be lazy and not have to type
>> > the colon. How do I need to format the cell?
>> > Thanks... Mike

>>


  #9  
Old July 17th 06, 10:52 PM posted to microsoft.public.excel.misc
JE McGimpsey
external usenet poster
 
Posts: 4,624
Default Format cell as military time

However, note that this will also implement the same AutoCorrect in Word
and other Office apps, too.

In article >,
"Ragdyer" > wrote:

> This works for smart as well as lazy people:
>
> http://tinyurl.com/r3ngw

  #10  
Old July 17th 06, 11:10 PM posted to microsoft.public.excel.misc
RagDyeR
external usenet poster
 
Posts: 3,574
Default Format cell as military time

True, but can't beat it for timecard entry on a Monday morning.

The girls reverse it after they're done.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JE McGimpsey" > wrote in message
...
> However, note that this will also implement the same AutoCorrect in Word
> and other Office apps, too.
>
> In article >,
> "Ragdyer" > wrote:
>
>> This works for smart as well as lazy people:
>>
>> http://tinyurl.com/r3ngw


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell contents with custom time format rounded after double-click HumblePie Excel Discussion (Misc queries) 2 August 6th 05 08:07 PM
time calculation with military time Ron Thetford Excel Worksheet Functions 8 July 29th 05 07:24 PM
Imported Date & Time format with calcs. managed in excel from imrp Todd F. Excel Worksheet Functions 0 July 8th 05 09:03 PM
can't format cell - have tried unlocking and unprotecting griffin Excel Discussion (Misc queries) 1 April 5th 05 02:11 AM
calculating with a time format cell Mahnaz Excel Worksheet Functions 1 December 13th 04 11:21 AM


All times are GMT +1. The time now is 04:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.