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

  1. Enter time format as a simple number (like “900″ or “1500″).
  2. Format the cell to display an extra colon.
  3. 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 :)

4 Responses to “Automatic Excel Time Format Without Colon in Input”

  1. Jesper Rønn-Jensen Says:

    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

  2. tjensen Says:

    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

  3. Ronald Says:

    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

  4. Sandálias Personalizadas Says:

    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!