CODEWITHSUNDEEP
google-sheets
aarelovich
aarelovich

Reputation: 5556

Getting the hours of a duration type cell in google sheets

I have a cell with a duration type field that is marking 25:30:00. I need to convert this to a decimal type of hours. So I would need 25.5 hours. However I cannot find any way of getting the number of hours from the field. Splitting the string by : does not work in a custom function neither does the function HOUR. So how can I do this.

PD: I've found this question How do I get the number of days for a duration where the number of hours exceeds 24 in google sheets? which seems very close to what I want but I can't figure out how to adapt it to my needs. Any help would be apprecaited.

Upvotes: 4

Views: 3226

Answers (1)

user6655984
user6655984

Reputation:

Duration is represented in Google Sheets by a number that measures the duration in days. (You can see this by changing the format of a cell with duration to a number.) This is why multiplying it by 24, as pnuts suggested, gives the duration expressed in hours. I'd suggest also using N to make the result explicitly a number:

=N(A2*24)

(Assuming duration is in A2). This should eliminate the need to manually format the output as a number.

Upvotes: 10

Related Questions