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.

I found an answer on [superuser](https://superuser.com/questions/1036327/how-to-convert-a-duration-into-a-number-of-hours-in-google-spreadsheets) maybe this will help if you still need help. — ArcadeArchie, Jul 13 '19 at 18:43
There are other ways for similar approaches ([stackoverflow.com/a/72821500/2371987](https://stackoverflow.com/a/72821500/2371987)). Please be free to try other ways. For my side, it went well. — Francisco Maria Calisto, Jul 15 '22 at 09:45

Diego · Accepted Answer · 2018-04-05T14:38:49.117

49

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

edited Apr 05 '18 at 14:38

answered Apr 05 '18 at 14:10

Diego

9,261
2
19
33

2

Split method doesn't work in google sheets. Had already tried this. – TheOnlyAnil Apr 05 '18 at 14:32
1

@TheOnlyAnil Sorry, I was stuck in the Google Apps Script mentality. I've revised my answer. – Diego Apr 05 '18 at 14:39
1

@TheOnlyAnil Can you please provide a screenshot of your input (the value) and your script/formula? – Diego Apr 06 '18 at 08:37
1

Thanks I just had to create another column for this and it works quite fine. – TheOnlyAnil Apr 06 '18 at 08:42

score 23 · Answer 2 · edited Jul 02 '19 at 12:55

23

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.

edited Jul 02 '19 at 12:55

Charlie Bell

23
3

answered Jun 27 '19 at 23:46

AlphAcurA

231
2
2

3

Simple and accurate. Thx – LMSingh Jul 11 '20 at 00:12

score 5 · Answer 3 · answered Jun 01 '20 at 19:15

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

score 2 · Answer 4 · edited Apr 23 '18 at 18:26

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!

score 2 · Answer 5 · edited Jan 29 '20 at 22:34

2

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

edited Jan 29 '20 at 22:34

Das_Geek

2,775
7
20
26

answered Jan 29 '20 at 21:36

Mabast

21
1

2

Avoid code only answers. Provide a little explanation – Thangadurai Jan 30 '20 at 02:01

score 2 · Answer 6 · answered Feb 13 '20 at 18:13

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.

score 0 · Answer 7 · answered Oct 16 '22 at 17:23

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

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

7 Answers7

Google Sheets

Google Sheets API & Google Apps Script

Linked