«
»

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 :)

This entry was posted by Jesper Rønn-Jensen on Wednesday, January 5th, 2011 at 20:41 (GMT-1) and is filed under office. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

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

  1. Jesper Rønn-Jensen Says:
    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

  2. tjensen Says:
    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

  3. Ronald Says:
    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

  4. Sandálias Personalizadas Says:
    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!