Automatic Time Formatting

[gkarasiewicz]

Hello:

Just wondering how to go about having my cells automatically change 0800 into 08:00 so that I don't have input the : everytime.

Thanks for the help!

[Frinavale]

Hello:

Just wondering how to go about having my cells automatically change 0800 into 08:00 so that I don't have input the : everytime.

Thanks for the help!

What language are you implementing in?
What types of objects are your "cells"?

[coolgames]

please be specific..
i don't know what type of cells you are talking about...

Excel ??

[gkarasiewicz]

Excel cells

Sorry

[Scott Price]

In Excel 2003:

Right click the cell or range of cells, choose Format Cells. On the first tab in the dialog box that appears, choose Custom (bottom of list). Click on one of the formats, and then enter this: 00":"00 in the Type text box.

Regards,
Scott

[FishVal]

Nice, Scott. Much more better than the event handler I've almost posted. LOL.

[gkarasiewicz]

Okay that was simple and does the trick.

I coudl fall in love with ya'll over all this hehe

[Scott Price]

Glad it works for you!

The simpler the better IMHO :-)

Regards,
Scott

[Scott Price]

Nice, Scott. Much more better than the event handler I've almost posted. LOL.

Thanks, Fish!

Would be interesting to see the event handler, though... It could probably have a few added features that make it more powerful?

Regards,
Scott

[FishVal]

Something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Static blnIsExecuted As Boolean
    
    If blnIsExecuted Then Exit Sub
    
    With Target
        'Somewhat speculative cell format check, should be smthng else
        If InStr(1, .NumberFormat, "h:mm") > 2 Or _
            InStr(1, .NumberFormat, "h:mm") < 1 Then Exit Sub
        blnIsExecuted = True
        .Value = Int(.Value / 100) & ":" & Format(.Value Mod 100, "00")
        blnIsExecuted = False
    End With
    
End Sub

[Frinavale]

Something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Static blnIsExecuted As Boolean
    
    If blnIsExecuted Then Exit Sub
    
    With Target
        'Somewhat speculative cell format check, should be smthng else
        If InStr(1, .NumberFormat, "h:mm") > 2 Or _
            InStr(1, .NumberFormat, "h:mm") < 1 Then Exit Sub
        blnIsExecuted = True
        .Value = Int(.Value / 100) & ":" & Format(.Value Mod 100, "00")
        blnIsExecuted = False
    End With
    
End Sub

I didn't even know Excel had event handlers :P
I'm going to have to take a look at it more!

[NeoPa]

I typically use a global flag to indicate that the code is already running, as other routines also need to know, but the static internal one also works well in this specific case.

'This routine ensures the figure is only updated when appropriate
'It relies on a variable (blnBlock) defined (as Public) in a standard code 
'module within the project.  You can add other criteria if required.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  With Target
    If blnBlock _
    Or InStr(1, .Address, ":") > 0 _
    Or InStr(1, .Address, ",") > 0 _
    Or InStr(1, .Value, ":") > 0 Then Exit Sub
    blnBlock = True
    .Value = Format(.Value, "00:00")
    'If a text result is explicitly required use the following line instead
    '.Value = Format(.Value, "'00:00")
    blnBlock = False
  End With
End Sub

[NeoPa]

I didn't even know Excel had event handlers :P
I'm going to have to take a look at it more!

If you look in the VBA window (Alt-F11) you will see a couple of drop-downs in the Code Pane (F7). The one on the left allows you to select an object and then the one on the right lists the possible events to handle. If you select one it will even create a skeleton routine for you (empty of course).