Auto-Converting Input

[ipanchuk]

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 sheet can then add these up and calculate hourly pay.

Could someone please tell me how to make that happen?

[#2]

@ipanchuk 

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 Sub

This 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).