|
Format Cells as Time in Excel
This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that instead of just numbers, an actual sort of digital clock display will appear. By default, this format is in hh:mm:ss AM/PM. This means the Time format will show the hour, the minute, the seconds, and the AM or PM designation in Excel.
If you are working with time in Excel, you need to use the Time number format. This macro allows you to quickly and easily apply this format to a large number of cells in Excel.
Where to install the macro: Module
Excel Macro to Format Cells as Time in Excel
Sub Format_Cell_Time()
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Excel is selecting two cells at a time. Does anyone know how to select one cell?
Every time I try to format cells excel frezes. This also happens if i try to change type to bold etc. i am getting no codes or anything. Any ideas?
I currently have a spreadsheet with mutliple worksheets. We noticed that the formatting for all cells changed to TIME format even though the cells might have been DATE, NUMERIC, or just GENERAL. Our user says they did not change the formatting. Wondering if any EXCEL 2007 does an automatic update that I am not aware of??? We are starting to use 2007 and not has familiar. Any thoughts are appreciated.
Hi,
I am exporting a table from Access into Excel using vba as it is adding the data into an existing spreadsheet.
The problem is the column that "Time" is going into all the values read 00/01/1900
In the Access table it is formatted as a "Time Field", and if I change the format in the excel column to "Time" manually the results are ok, but I do not want to have to change this each time as all the other columns are correctly formatted!
Also I have saved the spreadsheet with the column formatted as "Time" but this seems to be being ignored!
Any ideas would be appreciated.
Thanks
hi can anyone tell me how to merge cells as shown in the following?
From a list of timings:
10|30|2 to merge into 10:30:02
10|4|44 to merge into 10:04:44
thanks peeps =)
I have a large list of times. There are in fact how long a call lasts.
Unfortunatly they are in the wron format. The format is 00:00:00 hours,
minutes, seconds.
I have changed their format to time, but when I sum them I get 00:00:00 as
the result.
But if I double click on a cell then click off it again the formatting
changes. The entries go from being left justified to right and the sum
function now works. In the undo drop down it say it wants to undo Typing
'0:01:03' in N4 for example.
How can I do this automaticaly because I have about 12,000 entries?
I have a spreadsheet that was imported from a .csv file and some dates/times
appear in different formats:
5/28/06 12:00
5/29/06 0:00
5/29/06 12:00
5/30/06 0:00
5/30/06 12:00
5/31/06 0:00
5/31/06 12:00
1-JUN-2006 00:00
1-JUN-2006 12:00
2-JUN-2006 00:00
2-JUN-2006 12:00
3-JUN-2006 00:00
3-JUN-2006 12:00
(down through 9-JUN-2006 12:00)
6/10/06 0:00
6/10/06 12:00
6/11/06 0:00
6/11/06 12:00
....
I've tried selecting the entire column and format it using right-click,
Format Cells/ Number/Date then mm/dd/yy hh:mm and all other cells format
correctly, with the exception of the x-JUN-2006 cells. Even if I format
the entire column to just Time, all cells except the x-JUN-2006 files will
change to just the time.
I'm running Excel 2003 SP2.
Thanks in advance!
I would like to format a row of cells to military time without seconds in Excel 2007. I have tried many things, but I am struggling. Please help!
I have a workbook with a number of pivot tables that I update daily. Whenever I open the workbook all number formats have changed from number wit no decimal places to an hh:mm:ss format always set to 00:00:00 I have to reset all columns every time and I'd rather not !
Hi all,
I have a question:
I am working with about 24k rows of data in a time format of DDD:HH:MM:SS (example: 001:08:22:31 is 1 day 8 hours 22 minutes and 31 seconds) I need to format these cells with a custom format so that I may run a autosum of all the rows and get a result that is in a similar format. I can not for the life of me to get the autosum to give me a valid result. I believe my issues lies with my cell format. Can someone please help?!
here is a very small portion of my data if it will help:
000:00:16:55
000:00:06:43
000:00:11:45
000:00:49:37
000:00:09:11
|
|
