Display time in hours?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hello Jennifer,

You could test following event macro

Code:
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
 
Upvote 0
Hello Jennifer,

You could test following event macro

Code:
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
 
Upvote 0
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".
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top