Customise time format for "army" time

[tris_r]

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

[Steel Monkey]

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

=IF(LEN(A1)=3,LEFT(A1,1)&":"&RIGHT(A1,2),LEFT(A1,2)&":"&RIGHT(A1,2))

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

Hope this makes sense

Let me know how you go!

[tris_r]

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.

[Dean England]

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.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a2:b20")) Is Nothing Then Exit Sub
UserInput = Target.Value
On Error Resume Next
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub

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