I do a lot of time calculating that involves start time subtracted from stop time. Example: start - 9:00, stop - 10:15. Is there any way I can just enter the time as straight numbers instead of always having to use the colin? I am currently using use Excel 2003. Soon to be using Excel 2010.

---

Reply from Richard Rost:

Sure. Here's what I would do. In column A enter your times as numbers. Now, this example assumes that you'll type in times as either 3 or 4 digit values, like this:

130
1245
1730
250
2100

Now in column B we can use some function trickery to convert that into DATE/TIME values, based on the number of characters in each value (3 or 4):

=IF(LEN(A1)=3,TIME(LEFT(A1,1),RIGHT(A1,2),0),TIME(LEFT(A1,2),RIGHT(A1,2),0))

Basically this says: IF there are 3 characters in the time, then set the hour equal to the leftmost character, the minute equal to the right 2 characters, and the seconds to 0. OTHERWISE, the hour is the left 2 characters, the minute is the right 2 characters. I cover the IF function in Excel Expert Level 3.

The TIME function takes individual components (hour, minute, second) and builds an actual DATE/TIME value out of it. I cover this function (and the other date/time functions) extensively in Excel Expert Level 2.

Now you're left with a series of date/time values but remember that Excel stores dates as a number of DAYS, so your column B now looks like this:

0.0625
0.53125
0.729166667
0.118055556
0.875

That's OK. That's exactly what is expected. Now just right-click on the column header and select Format Cells > Time > and pick the "01:30" time format.

There you go.

1:30
12:45
17:30
2:50
21:00

Hope this helps. (Hmmm... this would make a nice mini video tutorial. I'll have to add it to my list).