## Automatic Excel Time Format Without Colon in Input

For a simple Excel sheet time reporting, i needed to enter hours every day for arrival and departure time.

Excel is not so smart about the time format. For instance I need to enter “9:00” for nine in the morning. I wanted just to enter “900”, and let the excel sheet format the data correctly itself.

I found a working solution on PC Mag: “Easy Date and Time Entry in Excel Tips“.

- Enter time format as a simple number (like “900” or “1500”).
- Format the cell to display an extra colon.
- In a (hidden?) cell, calculate the time to use for further calculations.

#### Format cells for viewing:

Take the cell and format it so it visually shows the colon for the number that is input. Go to the menu item “Format” > “Cells…” dialog. Select “Custom” format and input “0\:00” as shown on the screenshot.

#### Create (hidden)? cell to calculate time:

Take an adjacent cell and let it interpret the input value as a time value. `=TIME(9, 15, 0)`

is equivalent to 09:15:00 and will require 3 parameters for hour, minutes, seconds.

Our inputformat is “915”. Extract hours value: `TRUNC(915/100)`

Extract minutes value with modulus that extracts the rest 15 from 915 divided by 100: `MOD(915, 100)`

So the formula will be `=TIME( TRUNC(915/100), MOD(915,100), 0)`

This calculation is hidden in the final spreadsheet in columns E, G, and J.

Here a screenshot of the result:

I added the time sheet template here for further convenience.

2011-timesheet.xls (47KB Excel format)

(timesheet updated January 6th: Sum can now handle more than 24 hours)

IAnd now I can happily save entering colons every day :)

January 6th, 2011 at 12:33 (GMT-1)

Updated the sum calculation. If it got to 24 hours it would continue from 00:00. The solution was to change the format of the sum field. New (custom) format “[h]:mm” solved the problem.

I found the solution in this Excel Dates and Times FAQ

January 15th, 2011 at 11:10 (GMT-1)

what about a macro insert a “:” with a simple substring right 2 chars replace.

in global.dot works in every sheets no hidden collums etc

January 26th, 2011 at 06:10 (GMT-1)

Thanks for sharing this one this is very helpful especially to the people who are looking for this topic. This is very informative like this site I’ve discovered earlier http://www.judithbassler.com/blog/include-the-dates-and-times-in-excel.html

February 6th, 2011 at 14:34 (GMT-1)

what about a macro insert a “:” with a simple substring right 2 chars replace.

in global.dot works in every sheets no hidden collums etc.

thanks!