Cell format changing on the fly
 

Is there a way to format a cell so that if you enter a time, such as
entering :30 into A3, and having Excel automatically changing that
value to decimal, in this case, .50?

So if I enter :30, hit enter, and it changes to .50 in the same cell?

Thanks! :cool:


--
bellz
------------------------------------------------------------------------
bellz's Profile: http://www.excelforum.com/member.php...o&userid=30429
View this thread: http://www.excelforum.com/showthread...hreadid=500990

Cell format changing on the fly
 

Anybody have any ideas?


--
bellz
------------------------------------------------------------------------
bellz's Profile: http://www.excelforum.com/member.php...o&userid=30429
View this thread: http://www.excelforum.com/showthread...hreadid=500990

Cell format changing on the fly
 

Bellz,
First of all, (at least my version of) Excel does not accept :30 as
time. It is accepted as text.

With this said, your job can be done with an event macro:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Left(Target.Value, 1) = ":" And IsNumeric(Mid(Target.Value, 2,
Len(Target.Value))) Then
Target.Value = CInt(Mid(Target.Value, 2, Len(Target.Value))) / 60
End If
End Sub

Press Alt+F11 to bring up the VBA IDE.
At the top left is the Project Explorer window.
Under the workbook you want to work with there is the icon
ThisWorkbook.
Double-click the icon to bring up the VBA code page for the workbook.
Paste the above code.

HTH
Kostis Vezerides