How to convert Time into decimal float in Google Sheets using Script?

Question

I want to convert the time HH:MM into H.xx

Like I am getting it in this format: Sat Dec 30 00:00:00 GMT+05:21 1899

But this value is 04:29 in cell. I want it to be 4.5 hours to multiply it to hourly rate.

Answer 1

Google Sheets

In Google Sheets, if you have a date/time value in a cell (e.g. "D9"), then use =HOUR(D9)+(MINUTE(D9)/60).

If the value is stored in the format 04:29, then use =INDEX(SPLIT(D9, ":"), 1) + (INDEX(SPLIT(D9, ":"), 2)/60).

---

Google Sheets API & Google Apps Script

If you want to use the Google Sheets API or Google Apps Script, then you can use javascript.

You need to use the getMinutes() method and divide by 60, then add that to the hour (using getHours()).

var date = new Date(); var minutes = date.getMinutes(); var output = date.getHours() + (minutes/60); 

Be aware that this is ignoring seconds.

If the value in the cell is stored as a string like 04:29, then you'll need to split it.

var time = "04:29"; var hour = Number(time.split(":")[0]); var minutes = Number(time.split(":")[1]); var output = hour + (minutes/60);