I’m working on a timesheet in Google Sheets and need help with converting time values to decimal format using Apps Script.
In my spreadsheet, I have time entries like MM:HH that I need to transform into decimal numbers like M.xx.
For example, when I read a cell containing 04:29, the script returns something like Sat Dec 30 00:00:00 GMT+05:21 1899. However, I want to convert that 04:29 value into 4.5 decimal hours so I can multiply it by an hourly wage rate.
What’s the best way to handle this time conversion in Google Apps Script? I need the result as a decimal number that represents the total hours including the fractional part.
Google Sheets represents time as a decimal part of a day, which can lead to confusion. To convert a time value like 04:29 to decimal hours, you can retrieve the value using the getDisplayValue() method in your Apps Script. After that, split the string by the colon to separate hours and minutes. Convert those parts to numbers and then calculate the decimal hours by adding the hours to the minutes divided by 60. For example, this means 4 + (29/60) gives approximately 4.48 hours. Alternatively, if necessary, work with the date object and use getHours() and getMinutes() for extraction, and apply the same conversion for accuracy.
I ran into this exact issue last month when building a payroll calculator. The trick that worked for me was using the raw cell value and multiplying by 24, but I had to be careful about how the time was originally entered. If you’re getting that weird date object, it usually means the cell is already interpreted as a time value by Sheets. In that case, just grab the value with getValue() and multiply by 24 like CharlieLion22 mentioned. However, if your times are stored as text strings, you’ll need to parse them manually. I found that using Utilities.formatDate() can also help normalize the values before conversion. The key is being consistent with your input format - either all text strings or all proper time values, otherwise you’ll get mixed results when processing multiple cells.
yep, exactly! just remember, multiplying by 24 converts it to hours. if A1 is your cell, then var decimalHours = sheet.getRange('A1').getValue() * 24; will give you that 4.48 straight up! super easy!