Forum Discussion

ipanchuk's avatar
ipanchuk
Copper Contributor
Jul 02, 2024

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

Could someone please tell me how to make that happen?

Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 02, 2024

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

Resources