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

 

[James006]

Hello Jennifer,

You could test following event macro

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
    .Value = .Value * 24
    .NumberFormat = "General"
End With
Application.EnableEvents = True
End Sub

Hope this will help

 

[JenniferMurphy]

Hello Jennifer,

You could test following event macro

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
    .Value = .Value * 24
    .NumberFormat = "General"
End With
Application.EnableEvents = True
End Sub

Hope this will help

That macro will change the value in the cell, right?

I was hoping for a way to leave the value as is (in days), but display it in hours. I guess I'll use a separate cell for the value.

Thanks

 

[James006]

In your first message, you were saying

can I do it in the same cell ?

Can you explain what should be the final result ... in the same cell ...

 

[Tetra201]

... I'd like to enter "7:45" in a cell and have "7.75" displayed in that cell...

The closest I can think of would be displaying "775": format the cell as ?/2400, then enter Ctrl+J before the slash.

 

[JenniferMurphy]

Can you explain what should be the final result ... in the same cell ...

I was hoping that the cell could continue to contain the value read off the stopwatch (7:45), but display the time in hours (7.75). I was thinking of the way 0.6666666666667 was keep that value, but be displayed as "0.67".

 

[JenniferMurphy]

The closest I can think of would be displaying "775": format the cell as ?/2400, then enter Ctrl+J before the slash.

That's interesting. When I try that, the cell shows 775/2400. What does the "/" + Ctrl-J + "2400" do?

 

[Tetra201]

Here is a step-by-step guide:

- Enter 7:45 in a cell;
- Enable "Wrap Text" in that cell;
- Open "Format Cells" dialog and select Custom;
- In the Type field, enter ?/2400;
- In the Type field, position the cursor between the ? and the /;
- Press and hold Ctrl, then press J -- to insert a line break;
- Now, you should see only the ? in the Type filed;
- Click "OK" -- you should see only 775 in the cell;
- If you increase the row height, you should see /2400 on the second line.

 

[JenniferMurphy]

Here is a step-by-step guide:

OK, that gets the display you show, but can you explain what the format codes are actually doing?

I thought the "?" was a digit placeholder, like "0" but for spaces.

What do the "/" and the "2400" do?

Thanks

 

[Tetra201]

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.