# Questionconverting time to percent (I think)

Page 2 - Seeking answers? Join the Tom's Hardware community: where nearly two million members share solutions and discuss the latest tech.

#### Bbud

=INT(Q7)&":"&(60*(Q7-TRUNC(Q7)))

In cell F7:
The number with only 1 zero is a calculated field, with Q7 (hidden cell) = 2.00.

I've tried formatting those cells in column F to 1234.00 (two digits past period), but no dice!

#### kanewolf

##### Titan
Moderator
View: https://imgur.com/a/v9Wlyv8

Note that there are no problems with values other than those that end in :0.
If I alter the time in column E by 15 minutes, the calculation completes with no problem. I don't get it.
You want column F formatted at TIME not a number. It is column F that needs the update.

Bbud

#### Bbud

Column F is formatted as Time (00:00)

The value in Q7 is 2.00, not 2:00. Not sure why all the other values carry over two digits past period, but those ending in 00 drop a zero?

#### kanewolf

##### Titan
Moderator
Column F is formatted as Time (00:00)

The value in Q7 is 2.00, not 2:00. Not sure why all the other values carry over two digits past period, but those ending in 00 drop a zero?
If that is the case then the cell that is having problems "2:0" must have a single quote or some other special character that is causing it to be text rather than time.

Bbud

#### lvt

##### Commendable
I don't have MS Excel so I can't make more tests, but in my OpenOffice Calc (Excel equivalent) it works like a charm.

Anyway you have the right formula, just try to figure out the problem with data type in your Excel.

Bbud

#### Alabalcho

##### Titan
Guys, you are making this much harder than it has to be.

In Excel, date / time is stored as a number, with whole part representing number of days since some date, and fractional part representing the time. So, if you get just the fractional part of the cell where time is stored, you'll get the time. Multiply by 24, and format it anyway you want - number of hours, like 03:30 resulting in 3.50.
So, if A1 keeps the time, enter following in B1:
Code:
``24*(A1-trunc(A1))``

Bbud

#### lvt

##### Commendable
Guys, you are making this much harder than it has to be.

In Excel, date / time is stored as a number, with whole part representing number of days since some date, and fractional part representing the time. So, if you get just the fractional part of the cell where time is stored, you'll get the time. Multiply by 24, and format it anyway you want - number of hours, like 03:30 resulting in 3.50.
So, if A1 keeps the time, enter following in B1:
Code:
``24*(A1-trunc(A1))``
In order to use your method, one must assume that TIME is a number, and the TRUNC function is needed.

Most of other methods respect the data type of TIME value and don't require an extra function, it's preferable because the same function might not work the same way across version or platform.

Bbud

#### Alabalcho

##### Titan
TIME values are really a number, not a string, and it's much easier converting a string to TIME format than all the gimmicks (although working) shown.

Works for MS, works for Google Sheets. Don't have OpenOffice' Calc in hand to try, bu I suppose it will work as well.

Last edited:
Bbud

#### Bbud

Thanks all! I have some work to do now. I'll let you know how it all worked out.