Customise time format for "army" time

Hello people

I put together a basic time sheet for myself that I use on my PDA and laptop. It's not a big deal but entering in the colon for say "8:30" is a bit of a pain and "830" or even "0830" would be easier.

I tried a custom cell value of ##":"## and that displayed "830" as "8:30", but if you work with these numbers (ie total up hours worked) excel treats them as full numbers.

Is there a custom cell format value to allow me to enter in "830" and it treat it as the time value for "8:30" so that I can work with the times.

Thanks

I dont think you can do this(someone please correct me if im mistaken) as if you dont include the : then excel thinks you are giving it a serial date. However i do believe you can get around it.

If you want to enter the time ie 0830 into A1 do the following:
-format A1 as text and then
-put the below formula into B1

Please Login or Register  to view this content.

You should then be able to work with the times in B1

Hope this makes sense

Let me know how you go!

Thanks for the work around Steel Monkey. I tested the formula and it worked but the thing is that I wanted to keep it simple to use on my PDA. Having separate input and output cells for everything was getting a bit messy. Besides I suppose that the simple work around is to just put in the colons.

tris_r,

Here's a little Worksheet Change Event that I picked up somewhere. I pick up so many I sometimes forget where. It works for me.

Please Login or Register  to view this content.

Cols A and B are formatted General and Col C is formatted h:mm for the difference. To place in the sheet-Right Click on the sheet tab, click view code and copy this code into the box. You can enter all the times in Col A and Col B from your keypad (800, 1000, 1130, etc and they will come out 8:00, 10:00, 11:30, etc.) I've attached a small zip file.

Hope this helps.

Dean