Jump to content
Macro Express Forums

Time Formatting


Recommended Posts

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

Link to comment
Share on other sites

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'.

Link to comment
Share on other sites

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%"/>

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...