Format by calculation?

[HarryOhm]

Hello all,

This is my first post here, so hope this goes well.

Is there any way that Excel can take a number entered into a cell, use that number in a calculation and then display the result in the same cell?

Say I have a time value of 34:45 or thirty four hours and fifty six minutes.

What I want to do is enter the time value as a 4 digit number (as in 3445, without the colon) and have Excel convert it to decimal time (34.75) and display this result in the same cell that I originally typed the "3445" in.

Is this even possible? Its easy to force a format, but how to do the conversion as part of the formatting process is the trick.

I sure hope so as it would make things a lot easier.

Thanks.

Harry Ohm.

 

[doofusboy]

Here's one way to do what you described [assuming you enter the value in cell A1].

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
        Application.EnableEvents = False
        ' do your calculation wizardry here
        Target.Value = WorksheetFunction.Fixed(Left(Target.Value, (Len(Target.Value) - 2)), 0) & "." & Split((Right(Target.Value, 2) / 60), ".")(1)
        Application.EnableEvents = True
    End If
 
End Sub

 

[pgc01]

Hi Harry
Welcome to the board

The problem that you describe is similar to the one of typing a long list of amounts including cents, and you want the decimal point to be inserted automatically.

When you have to do it you set the option "Automatically insert a decimal point" and then reset it when you're done. This way you type 1234 and excel autmatically writes 12.34 in the cell (if you've chosen 2 decimals).

This is only interesting, of course, if you have to enter lots of values, else it's simpler to type the dot.