Question converting time to percent (I think)

May 14, 2021
31
0
30
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.
 
Last edited:

kanewolf

Titan
Moderator
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.
=HOUR(A1)+(MINUTE(A1)/60)+(SECOND(A1)/(60*60))
 
  • Like
Reactions: Bbud
May 14, 2021
31
0
30
Thanks to you both for your responses.

Sizzling - your suggestion creates the following:

F5=0:30 (30 minutes)
response creates 30.
What I'm looking for is .5 for half an hour.

Eximo - your suggeston creates the following in another example:
F5=0:45 (forty five minutes)
response creates .50
Not sure what's going on here. Is :45 minutes being rounded to .5 hour?

The other problem I'm going to have is adding this column of numbers in a format that will give me 45.5 hours for example.
 

USAFRet

Titan
Moderator
Thanks to you both for your responses.

Sizzling - your suggestion creates the following:

F5=0:30 (30 minutes)
response creates 30.
What I'm looking for is .5 for half an hour.

Eximo - your suggeston creates the following in another example:
F5=0:45 (forty five minutes)
response creates .50
Not sure what's going on here. Is :45 minutes being rounded to .5 hour?

The other problem I'm going to have is adding this column of numbers in a format that will give me 45.5 hours for example.
You need to parse off the minutes and divide that out individually.

=30/60 results in 0.5
=45/60 results in 0.75
 
May 14, 2021
31
0
30
Tested with OpenOffice Calc, it works fine for everything :

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

A1 is my date cell used for example.



8:00 => 8.00
9:45 => 9.75
1:30 => 1.5

My whole hour values (1.0, 2.0, etc) come back with the same error message illustrated above
Tested with OpenOffice Calc, it works fine for everything :

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

A1 is my date cell used for example.



8:00 => 8.00
9:45 => 9.75
1:30 => 1.5
Tested with OpenOffice Calc, it works fine for everything :

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

A1 is my date cell used for example.



8:00 => 8.00
9:45 => 9.75
1:30 => 1.5

Works will with everything except whole numbers (1:0, 3:0, etc). Returns same response:

#VALUE! Function MOD parameter 1 expects number values. But '2:0' is a text and cannot be coerced to a number.
 
My whole hour values (1.0, 2.0, etc) come back with the same error message illustrated above



Works will with everything except whole numbers (1:0, 3:0, etc). Returns same response:

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

Did you format your cells correctly?

The Time column must be HH:MM

The Decimal column must be Number with 2 decimals.
 
  • Like
Reactions: Bbud
Screenshot of my example cited above :

sUOw5a0.jpeg
 
  • Like
Reactions: Bbud and USAFRet