Display time in hours?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,540
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

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,540
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,369
Messages
5,444,058
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top