CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
nowiko
nowiko

Reputation: 2567

Formatting cell value in Google Sheets

I have number of hours 02:30, and I want to parse it to 2.5 automatically when I place it to cell. How can I accomplish this?

Upvotes: 0

Views: 82

Answers (2)

Chris Hick
Chris Hick

Reputation: 3094

Does this formula work as you want:

=VALUE("02:30")*24

You can replace the "02:30" with a cell reference or a formula that outputs that value

Upvotes: 1

Let Me Tink About It
Let Me Tink About It

Reputation: 16122

This is a four-step process. You need four formulas. Broken down into steps they are as follows.

Start by placing 2:30 in cell A1 then proceed as follows.

A2=HOUR(A1)   // Step 1. Get hours
A3=MINUTE(A1) // Step 2. Get minutes
A4=A3/60      // Step 3. Get minutes as fraction of hour
A5=A2+A4      // Step 4. Add step 1 result to step 3 result

Or you could combine it all into one big function:

B1=HOUR(A1)+(MINUTE(A1)/60)

Here is a link to the spreadsheet that proves the above is correct.

Upvotes: 1

Related Questions