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.

Candicandia

I found an answer on superuser maybe this will help if you still need help. – Hindustani

There are other ways for similar approaches (https://mcmap.net/q/160221/-how-to-convert-duration-of-mm-ss-to-seconds-in-google-sheets). Please be free to try other ways. For my side, it went well. – Pregnancy

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

Davin

Split method doesn't work in google sheets. Had already tried this. – Candicandia

@Candicandia Sorry, I was stuck in the Google Apps Script mentality. I've revised my answer. – Davin

@Candicandia Can you please provide a screenshot of your input (the value) and your script/formula? – Davin

Thanks I just had to create another column for this and it works quite fine. – Candicandia

Answer 2

Just Multiply your Duration by 24.

Example:

Time In     | Time out   | Duration | Decimal
11:45:00 AM | 3:40:00 PM | 3:55:00  | 3.92

You also have to alter the format of the cell containing the decimal from Automatic to Number in order for this to work in Google Sheets.

Yoko

Simple and accurate. Thx – Sounding

Answer 3

All you need is TO_PURE_NUMBER()
https://support.google.com/docs/answer/3094243?hl=en

Example:

// The following
A1=DATEVALUE("2020-05-18 06:09:18.821 ")+TIMEVALUE("2020-05-18 06:09:18.821 ")
A2=TO_PURE_NUMBER(A1)
A3=TO_DATE(A2)

// Results in the following, first result is just a formatted date
2020-05-18 6:09:19
43969.25647
5/18/2020

Poppycock

Answer 4

I tried the above methods with limited success, as I needed to have another cell to convert the time value to after calculation. However, I tried out a modification of this, and to my surprise, it works. I'll share my solution and hope it helps.

I have a Start Time and an End Time. Let's say my Start Time is in D6 and my End Time is in E6.

With the above formulas, I would have to have a cell (let's say F6) which does E6-D6, then another cell (let's say G6) to convert F6 from time to decimals.

I short-cut this by using:

=INDEX(SPLIT(E6-D6, ":"), 1) + (INDEX(SPLIT(E6-D6, ":"), 2)/60)

directly into F6 without the need for a G6 cell to convert.

Hope this helps!

Protestantism

Answer 5

=(INDEX(SPLIT(E17, ":"), 1) &"."& (INDEX(SPLIT(E17, ":"), 2))) * hourlyRate

Frigidarium

Avoid code only answers. Provide a little explanation – Ruthenium

Answer 6

This seemed to work: =TEXT(D2-C2,"h:mm")*24

Where D2 is End Time and C2 is Start Time,

The =TEXT(D2-C2,"h:mm") part both subtracts the times and converts the result into time format. The * 24 part makes it into a usable decimal instead of time format. So, in my case, I had Start Time as 12:30 pm and End Time as 1:20 pm. The Sheets was seeing the difference as :50, which it was interpreting as 12:50am. But when you multiply by 24, it then works out, giving you .833333333.

I tested it with Start Time 10am and End Time 12pm and got 2.000000000 which is also correct.

Oops I see now you wanted it using script. Sorry. This is for within sheets.

Exploratory

Answer 7

First of all - this thread is sure useful. It took me a couple of answers to understand what am I supposed to do, So.. I'm sharing my experience + images.. yay

What you should do:

• Select format --> number --> automatic (see image below)
• Insert your hours values (each value in a different cell)
• Subtract time values to represent the difference between them (in time format)
• Use timevalue function to represent the the difference as a decimal number
• Multiply the value with 24 in order to get the decimal representation in hours

Here's an image that displays the values I've got

And here's the formulas (assuming that time values are in columns A and B)

Column C  -->  B - A           for C1 formula is B1-A1, C2 formula is B2-A2 etc.
Column D  -->  timevalue(C)    for D1 formula is timevalue(C1), D2 formula is timevalue(C2) etc.
Column E  -->  D * 24          for E1 formula is D1 * 24, E2 formula is D2 * 24 etc.

or if you prefer this as a single formula

timevalue(B - A) * 24

Hope that I've clarified myself enough and that your are going to solve this even if you are not a software developer. Cheers

---

Josiejosler