Question converting time to percent (I think)

[Bbud]

I have a column of numbers in HH:mm format (2:15, 3:00, 1:45, etc). In order to convert 2:15 to a percent (2.25 hrs), I applied the following.

=hour(mod(F5,1)) + int(F5)*24 + minute(F5)/60

This works fine for anything other than whole hours (1:0, 2:0, 5:0, etc).

I'm not sure if it's the zeros that for formula doesn't like, or what.

Returns the message: #VALUE! Function MOD parameter 1 expects number values. But '2:0' is a text and cannot be coerced to a number.

Any help would be appreciated.

 

[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]

https://imgur.com/a/v9Wlyv8

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]

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]

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 ?

 

[lvt]

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.

 

[Alabalcho]

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:

24*(A1-trunc(A1))

 

[lvt]

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:

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.

 

[Alabalcho]

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.

 

[Bbud]

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