forum viagra achat acquisto cialis net vente generique viagra cialis donne tarif medicament levitra viagra aangeboden équivalent viagra sans ordonnance farmaco viagra generische variant van viagra cialis kopen in nederland viagra rezeptfrei auf rechnung viagra herbal compra de viagra generico levitra madrid levitra bayer 10 mg
livraison viagra rapide 
cialis pharmacie en ligne 
achat cialis france 
cialis en vente 
viagra 25 mg 
sildenafil 100 pilules 
acheter silagra 100 
generique cialis pas cher 
achat viagra generique pas cher 
quebec viagra 
prix viagra cialis 
viagra homme forum 
generic cialis online 
achat de viagra 
viagra super active plus 
viagra mastercard priligy suisse viagra naturel kamagra tablets kamagra price kamagra billigast kamagra inde forum finasteride mg viagra price cialis where to buy cialis over the counter cheapest generic viagra kamagra paypal viagra online order levitra

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!