Excel 07: How to type time without :

[MsCynic]

Hi guys, when typing time in a timesheet, how can I avoid having to use the : button? My time is already set in Vista to use . as a separator but I can't seem to get Excel to recognise 11.20 as 11.20am. It insists on having 11:20. When I do type 11:20 Excel converts this to read 11.20.00.a.m. but it still doesn't help as I have to type the : anyway.

I've gone into Custom and set a format that is hh.mm but this does not get interpreted by Excel as a time - especially difficult when I am using the worksheet to calcuate hours worked.

What am I doing wrong?

Thanks in advance,
Jo

 

[VoG]

If you Custom format the cell as

00\:00

then if you enter 1130 it will display as 11:30. However, the underlying value will be 1130.

 

[MsCynic]

Thanks for replying. When I tried that I got an error message saying that Excel could not display it and I should use one of the other formats.

In any case I need the cell to be recognised as hours and minutes because the next cell along converts the resulting time to decimal, which is how I am calculating the time to charge my client.

I'm really surprised this hasn't been an issue for other users - afterall who has a : button on their numeric keypad? Surely lots of us use Excel for calculating time.

J

 

[xenou]

One possibility:
http://www.mrexcel.com/articles/enter-time-without-colon.php

I'm not sure how commonly time is actually used in excel. I do so very rarely. In any case, it would be a difficult programming job to have excel figure out when you type a "." whether you mean for this to be a time separator or a decimal...and if a time separator whether you are working with decimals or minutes...maybe not as simple as it sounds.

A common strategy is to separate the hours input and the minutes input into different cells - using enter or tab would be quicker navigation from cell to cell than finding the colon key. There may be some freeware programs that handle time tracking also...if you want to get away from Excel on this.

Apropos of the above solution, you could maybe also format the cells as Text and use a formula to parse it out...

 

[MsCynic]

Thanks for the link. I suspect it will take me longer to figure that out than to type : seventy times but I'll look at it more closely once I've got this invoice done.

Yes, I've tried a few other timesheet tools too. The advantage of using Excel though is that I can send it to my client as is, just in case he ever wants to incorporate it into other worksheets of his own.

Cheers,
J