Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 152
Default adding a colon to military time

I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default adding a colon to military time


Try Custom format 00":"00

Vaya con Dios,
Chuck, CABGx3


"Suzanne" wrote:

I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default adding a colon to military time

=TEXT(A1,"00\:00")+0

format cell: hh:mm

"Suzanne" wrote:

I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default adding a colon to military time

On Thu, 6 Dec 2007 10:23:03 -0800, Suzanne
wrote:

I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?


1. Is the 1700 an Excel time formatted as hhmm, or is it merely the number
1700?

2. Do you want the result to be recognized by Excel as a Time, or just as a
number?

3. To change the format of 1700 so it "looks like" 17:00 (but the stored value
is still 1700) then select the cell(s) and
Format/Number/Custom Type: 00\:00

4. To change it to a value recognized as a Time by Excel, you will need a
formula (see below). Then Format/Number/Custom Type: hh:mm

Formula with 1700 in A1: =INT(A1/100)/24+MOD(A1,100)/1440


--ron
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default adding a colon to military time

You had a few replies giving you the right "image" of the time. If you require to calculate with the time, you need to make it
"real" Excel time: a fraction (of a day).

Is your time always 4 positions? ( i.e. no 745 for quarter to eight, but 0845)
If so, with the time in A1:

=TIME(INT(A1/100),MOD(A1,100),0)

Choose the appropriate format from the Format menu, or choose Format Custom, hh:mm

Post again in this thread if this doesn't answer your question

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Suzanne" wrote in message ...
|I need to add a colon to military time. I need to change it from 1700 to
| 17:00. I want it to stay in military time. I don't want am or pm to show.
| I can't figure out how to do this on the worksheet. Anyone know how to do
| this?




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 152
Default adding a colon to military time

This worked!!!! THANK YOU!!!!!

"CLR" wrote:


Try Custom format 00":"00

Vaya con Dios,
Chuck, CABGx3


"Suzanne" wrote:

I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default adding a colon to military time

Just as long as you are aware that this is not a real time.


Gord Dibben MS Excel MVP

On Thu, 6 Dec 2007 14:43:02 -0800, Suzanne
wrote:

This worked!!!! THANK YOU!!!!!

"CLR" wrote:


Try Custom format 00":"00

Vaya con Dios,
Chuck, CABGx3


"Suzanne" wrote:

I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default adding a colon to military time

On Thursday, December 6, 2007 11:23:03 PM UTC+5, Suzanne wrote:
I need to add a colon to military time. I need to change it from 1700 to
17:00. I want it to stay in military time. I don't want am or pm to show.
I can't figure out how to do this on the worksheet. Anyone know how to do
this?


bakwas; follow it; http://facebook.com/hatetharkipeople
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
Military time... NWO Excel Discussion (Misc queries) 3 March 24th 07 07:29 PM
military time TerryTutor Excel Discussion (Misc queries) 2 March 19th 07 03:50 PM
Show timesheet time in and out in regular time versus military tim John Excel Worksheet Functions 1 November 11th 05 06:14 AM
How can Excel do daily timesheet, -lunch time, & not use colon in. Jan Excel Worksheet Functions 2 March 8th 05 11:05 PM
adding military time format am6160 Excel Discussion (Misc queries) 2 December 28th 04 09:41 PM


All times are GMT +1. The time now is 03:35 PM.

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

About Us

"It's about Microsoft Excel"