Forum Discussion
Auto-Converting Input
Hi I'm working on an hours log excel sheet I need to have a column where someone can enter 20 (as in 20mins) and it will automatically be divided by 60 and represented as a decimal so that the sh...
To make it happen, you could use a VBA Worksheet_Change event handler
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = Target.Value / 60
Application.EnableEvents = True
End SubThis will divide any user input on the sheet by 60, but I think it is a bad idea to change user input. For example, 20 is changed to 0.333333. The user, seeing the result, may assume the value to be decimal hours and input 0.5 to represent 30min, only for Excel to convert the input to 0.008333.
It would be better to allow the input in minutes but then use a formula to convert the minutes to hours or even days (the latter being the spreadsheet standard for representing time).
