Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Military time... | Excel Discussion (Misc queries) | |||
military time | Excel Discussion (Misc queries) | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
How can Excel do daily timesheet, -lunch time, & not use colon in. | Excel Worksheet Functions | |||
adding military time format | Excel Discussion (Misc queries) |