Custom Number Formatting

  • Thread starter Thread starter BrainFRZ
  • Start date Start date
B

BrainFRZ

I'm trying to convert an inputted decimal into hours (not time). For example,
4.25 would be formatted to 4:15, 24.1 would be formatted to 24:06, etc, etc.
It is rather straightforward to do this as a function, but I don't want to
have to use a second column to convert it. Would anyone be able to help me?
Thanks.
 
I know this may sound dumb...I don't like having to use other columns for
formulas when I don't have to either but sometimes, well...
Anyway, I do my formula in a separate column of course and then perform this
action in the column that shows the results I want: I highlight that whole
results column, do "copy", "paste special" (rights over top of my results)
and click "values". It still perfectly shows your results so now you can
delete the column you used for your formula without messing up your results.
Of course the results are now just numbers, so they can't be updated without
going through the above process again.
 
This can't be done without a second column. Either enter 4:15, or divide by
24 in a helper column.

Regards,
Fred.
 
One way:

In an empty cell, enter 24. Copy the cell. Choose your "times". Choose
Edit/Paste Special, selecting the Values and Divide radio buttons. CLick
OK. Format the cells as Time.
 
I'm trying to convert an inputted decimal into hours (not time). For example,
4.25 would be formatted to 4:15, 24.1 would be formatted to 24:06, etc, etc.
It is rather straightforward to do this as a function, but I don't want to
have to use a second column to convert it. Would anyone be able to help me?
Thanks.
Click to expand...

I don't know what you mean when you write "hours (not time)". Hours ARE time.

If you want Excel to interpret your entry as hours, it will have to be
formatted as time.

If not, you could format it as a text string.

In order to do this, without using an adjacent column, you need to use a macro.
Below is an example of an event-triggered macro. To enter it, right click on
the sheet tab and select view code. Then paste the code below into the window
that opens. Note that r is set to whatever range you wish to have treated in
this manner.

===========================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("A:A") 'or whatever range to be processed
If Not Intersect(Target, r) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next 'ignore non-numeric inputs
Target.Value = Target.Value / 24
Target.NumberFormat = "[h]:mm"
On Error GoTo 0
End If
Application.EnableEvents = True
End Sub
============================
--ron
 
Back
Top