How to convert time to decimal format automatically...

  • Thread starter Thread starter alek23
  • Start date Start date
A

alek23

hi..pls correct if this is not the right forum, i have a big problem i
converting time to decimal automatically, i know the formula which i
"=A1*24", what i want is when i enter a time (8:34) in cell A1 an
press enter key it will convert to decimal (8.5667) forma
automatically. sorry if my term is wrong, i'm just quite a starter i
using Excel if any one help me with this problem, it's greatl
appreciated. i want to know this because almost my work in office i
entering time in Excel, i just want my work will be efficient
 
in order to do this, you'll need an event macro. See David McRitchie's
"Getting Started with Macros" if you need help:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Here's one way: Put this macro in your worksheet code module
(right-click the worksheet tab and choose "View Code".

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "A1" Then
If InStr(.NumberFormat, ":") Then
Application.EnableEvents = False
.Value = .Value * 24
.NumberFormat = "General"
Application.EnableEvents = True
End If
End If
End With
End Sub

This will only convert the number if you enter it as a time. So if you
enter 8.5, the value will be left alone, but if you enter 8:30, the
value will be converted to 8.5.
 
Back
Top