gschwede Posted March 17, 2009 Report Share Posted March 17, 2009 I have a problem when trying to copy a military time from excel into another program. For instance I want to be able to copy a string of times.. 0100, 0200, 0300, etc. into another workbook. I tried using notepad and word but could not figure it out. Excel's formatting does not allow the use of 0's before any numbers. Ideas? Thanks Quote Link to comment Share on other sites More sharing options...
kevin Posted March 17, 2009 Report Share Posted March 17, 2009 Excel's formatting does not allow the use of 0's before any numbers. You can paste numbers into Excel if you precede them with a ' like this '0100. Is there a cell formatting option in Excel that allows leading 0's? Quote Link to comment Share on other sites More sharing options...
jason Posted March 17, 2009 Report Share Posted March 17, 2009 You can set up a cell or column to allow leading zero's by clicking on on Format > Cell. From Format Cells window, click on Custom in the category list. You can then set the cell up to allow leading zero's. You can find formatting information in the Excel Help by searching 'leading zero'. Quote Link to comment Share on other sites More sharing options...
Cory Posted March 18, 2009 Report Share Posted March 18, 2009 Excel will guess something like 0900 is an integer. If you reformat it to be a date it will think it's 0000 6/18/1902, the 900th day after 12/31/1899. It's an Excel 'feature' one can not apparently turn off. The problem is you're pasting into cells formatted with the type "General". But when it guesses it throw stuff away so if you apparently lost anything in Excel's guess you can't get it back. There are two methods I could suggest depending on what you want to do. 1- You want to save the 0900 as simple text: Simply change the format of the target cells to Text and paste away. Excel will never 'interpret' plain text. FYI you the hot key in Excel for cell format is CTRL+1. And if you click the upper left gray block left of A and above 1 you can highlight the entire worksheet and reformat the entire thing as text. 2- You want to save the 0900 as an actual time value in Excel: This is the recommended solution. As you may know Excel and most programs save dates as serial time. You can read my article about it here. So 0900 is actually a decimal value of 0.375 Days. To let Excel know the 9 is the number of hours simply insert a colon using MEP. EG "09:00". Excel will make it an actual serial time value and it will now display as "9:00". If you want to display it as normal military time simply change the time formatting in Excel to Custom > "hhmm". Here's some sample script on how I would insert the colon: <VARIABLE SET STRING Option="\x02" Destination="%Clip%"/> <VARIABLE MODIFY STRING Option="\x09" Destination="%Hour%" Variable="%Clip%" Start="1" Count="2"/> <VARIABLE MODIFY STRING Option="\x09" Destination="%Minute%" Variable="%Clip%" Start="3" Count="2"/> <VARIABLE SET STRING Option="\x00" Destination="%Clip%" Value="%Hour%:%Minute%"/> <VARIABLE MODIFY STRING Option="\x10" Destination="%Clip%"/> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.