Question converting 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.
May 14, 2021
31
0
30
0
=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!
 
May 14, 2021
31
0
30
0
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.
Maybe a leading space ?
 
Reactions: Bbud

lvt

Commendable
Apr 19, 2021
1,725
276
1,640
39
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.
 
Reactions: Bbud
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))
 
Reactions: Bbud

lvt

Commendable
Apr 19, 2021
1,725
276
1,640
39
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.
 
Reactions: Bbud
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:
Reactions: Bbud

ASK THE COMMUNITY