Display time in hours?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,204
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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,204
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,204
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,204
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,490
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,204
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,490
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.
 

Forum statistics

Threads
1,089,589
Messages
5,409,162
Members
403,254
Latest member
Saiyan_8

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top