Display time in hours?

[JenniferMurphy]

Is there a way that I can enter a time value from a stopwatch as hh:ss and have it displayed as hours (hh.hh)?

For example, I'd like to enter "7:45" in a cell and have "7.75" displayed in that cell. I know I can do it by entering the "7:45" in A1 and use a formula in B1 (=A1*24) to convert it from days to hours and then format it as "0.00". But is there a way I can do it in the same cell?

Thanks

 

[JenniferMurphy]

Yes, the "?" is a digit placeholder, like "0" but for spaces.

The "?/2400" is a format for displaying numbers as simple fractions, namely, as twenty-four-hundredths. Similarly, "?/2" is a format for displaying numbers as halves, etc.

Interesting. Is this documented somewhere? I couldn't find it.

I ran a few simple tests.

[TABLE="class: grid, width: 467"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]CF->[/TD]
[TD="align: center"]?/1[/TD]
[TD="align: center"]?/2[/TD]
[TD="align: center"]?/3[/TD]
[TD="align: center"]?/4[/TD]
[TD="align: center"]?/10[/TD]
[TD="align: center"]#/10[/TD]
[TD="align: center"]0/10[/TD]
[TD="align: center"]00/10[/TD]
[TD="align: center"]0.0/10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1/1[/TD]
[TD="align: center"]2/2[/TD]
[TD="align: center"]3/3[/TD]
[TD="align: center"]4/4[/TD]
[TD="align: center"]10/10[/TD]
[TD="align: center"]10/10[/TD]
[TD="align: center"]10/10[/TD]
[TD="align: center"]10/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2/1[/TD]
[TD="align: center"]4/2[/TD]
[TD="align: center"]6/3[/TD]
[TD="align: center"]8/4[/TD]
[TD="align: center"]20/10[/TD]
[TD="align: center"]20/10[/TD]
[TD="align: center"]20/10[/TD]
[TD="align: center"]20/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3/1[/TD]
[TD="align: center"]6/2[/TD]
[TD="align: center"]9/3[/TD]
[TD="align: center"]12/4[/TD]
[TD="align: center"]30/10[/TD]
[TD="align: center"]30/10[/TD]
[TD="align: center"]30/10[/TD]
[TD="align: center"]30/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]1.5[/TD]
[TD="align: center"]2/1[/TD]
[TD="align: center"]3/2[/TD]
[TD="align: center"]5/3[/TD]
[TD="align: center"]6/4[/TD]
[TD="align: center"]15/10[/TD]
[TD="align: center"]15/10[/TD]
[TD="align: center"]15/10[/TD]
[TD="align: center"]15/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0/1[/TD]
[TD="align: center"]0/2[/TD]
[TD="align: center"]0/3[/TD]
[TD="align: center"]0/4[/TD]
[TD="align: center"]1/10[/TD]
[TD="align: center"]1/10[/TD]
[TD="align: center"]1/10[/TD]
[TD="align: center"]01/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0/1[/TD]
[TD="align: center"]0/2[/TD]
[TD="align: center"]1/3[/TD]
[TD="align: center"]1/4[/TD]
[TD="align: center"]2/10[/TD]
[TD="align: center"]2/10[/TD]
[TD="align: center"]2/10[/TD]
[TD="align: center"]02/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]0.25[/TD]
[TD="align: center"]0/1[/TD]
[TD="align: center"]1/2[/TD]
[TD="align: center"]1/3[/TD]
[TD="align: center"]1/4[/TD]
[TD="align: center"]3/10[/TD]
[TD="align: center"]3/10[/TD]
[TD="align: center"]3/10[/TD]
[TD="align: center"]03/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]1/1[/TD]
[TD="align: center"]1/2[/TD]
[TD="align: center"]2/3[/TD]
[TD="align: center"]2/4[/TD]
[TD="align: center"]5/10[/TD]
[TD="align: center"]5/10[/TD]
[TD="align: center"]5/10[/TD]
[TD="align: center"]05/10[/TD]
[TD="align: center"]error[/TD]
[/TR]
</tbody>[/TABLE]

A few observations:

1. It appears that it rounds the number on the left (rather than truncating).
2. The "#" and ")" codes also work (in addition to "?").
3. No other formatting codes are allowed to the left of the "/".

So what is this good for? I can't imagine where I'd use it?

 

[Tetra201]

... Is this documented somewhere?..

For example, here is a Definitive Guide to Custom Number Formats in Excel.

... So what is this good for?..

Well, this topic surfaced up as the closest approximation to displaying 7.75 upon entering 7:45 in a cell. What else? -- who knows...

 

[JenniferMurphy]

For example, here is a Definitive Guide to Custom Number Formats in Excel.

That's a very good article. Thanks.

 

[James006]

If your objective is purely ' Cosmetic '... :wink:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    If Target = "" Then
    Dim Sh As Shape
        For Each Sh In Shapes
           If Not Application.Intersect(Sh.TopLeftCell, Target) Is Nothing Then
             If Sh.Type = msoTextBox Then Sh.Delete
           End If
        Next Sh
        Exit Sub
    Else
        If IsNumeric(Target) Then
            Dim myTextBox As TextBox
            With Target
                Set myTextBox = .Parent.TextBoxes.Add(Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height)
            End With
            Application.EnableEvents = False
                With myTextBox
                    .Caption = Target * 24
                    .VerticalAlignment = msoAnchorCenter
                    .HorizontalAlignment = msoAnchorCenter
                End With
            Application.EnableEvents = True
        End If
    End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Target.Select
End Sub

 

[JenniferMurphy]

If your objective is purely ' Cosmetic '... :wink:

Wowie Zowie!!!

That is so far over my head that I can't even imagine how long it would take for me to comprehend what it is doing.

I give you the VBA Wizard award for this one.

 

[jasonb75]

Unless I'm having a full scale brainfart, a custom format of [h]\.mm should do what you're asking.

 

[JenniferMurphy]

Unless I'm having a full scale brainfart, a custom format of [h]\.mm should do what you're asking.

Unless I'm having one, all that does is replace the ":" with a ".". I want "7:45" to display as "7.75". That is, 7 hours and 45 minutes is to display as 7.75 hours. No?

 

[jasonb75]

And with that I'm going to leave quitely, hoping nobody noticed that I was here...

 

[JenniferMurphy]

And with that I'm going to leave quitely, hoping nobody noticed that I was here...

 

[James006]

Wowie Zowie!!!

That is so far over my head that I can't even imagine how long it would take for me to comprehend what it is doing.

I give you the VBA Wizard award for this one.

Thanks a lot for your very kind words ...