26

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.

anatoly techtonik
  • 17,421
  • 8
  • 111
  • 131
TheOnlyAnil
  • 710
  • 1
  • 13
  • 27
  • 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

6 Answers6

34

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);
Diego
  • 8,070
  • 2
  • 13
  • 26
14

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.

Charlie Bell
  • 23
  • 3
AlphAcurA
  • 141
  • 1
  • 2
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
Ray Foss
  • 2,905
  • 3
  • 20
  • 25
1

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!

Ryan Schaefer
  • 2,692
  • 1
  • 19
  • 41
Stu
  • 11
  • 1
1
=(INDEX(SPLIT(E17, ":"), 1) &"."& (INDEX(SPLIT(E17, ":"), 2))) * hourlyRate
Das_Geek
  • 2,284
  • 7
  • 16
  • 24
Mabast
  • 11
  • 1
1

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.

shaoutsi
  • 11
  • 1