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.

R/C	C	D	E	F	G	H	I	J	K	L
5	CF->	?/1	?/2	?/3	?/4	?/10	#/10	0/10	00/10	0.0/10
6	1	1/1	2/2	3/3	4/4	10/10	10/10	10/10	10/10	error
7	2	2/1	4/2	6/3	8/4	20/10	20/10	20/10	20/10	error
8	3	3/1	6/2	9/3	12/4	30/10	30/10	30/10	30/10	error
9	1.5	2/1	3/2	5/3	6/4	15/10	15/10	15/10	15/10	error
10	0.1	0/1	0/2	0/3	0/4	1/10	1/10	1/10	01/10	error
11	0.2	0/1	0/2	1/3	1/4	2/10	2/10	2/10	02/10	error
12	0.25	0/1	1/2	1/3	1/4	3/10	3/10	3/10	03/10	error
13	0.5	1/1	1/2	2/3	2/4	5/10	5/10	5/10	05/10	error

<tbody>
</tbody>

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 ...