prezzi finasteride cialis generika preisvergleich cialis bas prix achat de viagra cialis kopen propecia pas chere cialis alternatives køb cialis online viagra bas prix levitra filmtabletten 20mg acquista cialis generico precio levitra 20 mg cialis viagra levitra kaufen rezeptfrei cialis da 20 mg acheter cialis en pharmacie
prezzo finasteride cialis vente libre pris viagra trouver du viagra en france pris viagra cialis testen tadalafil kaufen cialis de 10 mg cialis seguridad social prix viagra belgique pharmacie en ligne belgique viagra generico italia viagra kostengünstig original cialis ohne rezept cialis generico india

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!