Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 17th 06, 08:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
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


  #2   Report Post  
Old July 17th 06, 08:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old July 17th 06, 09:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
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   Report Post  
Old July 17th 06, 09:24 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old July 17th 06, 09:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old July 17th 06, 09:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old July 17th 06, 10:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
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   Report Post  
Old July 17th 06, 10:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
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   Report Post  
Old July 17th 06, 10:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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   Report Post  
Old July 17th 06, 11:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10:21 AM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017