Question converting time to percent (I think)

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

Eximo

Titan
Ambassador
I think you mean Decimal time.

Still, a few ways to do it. My personal favorite for Excel:

=TEXT(A1*24,"0.00")

You can also wrap that in TIME or TIMEVALUE to convert to portions of a day and all kinds of stuff.
 
Reactions: Bbud
May 14, 2021
31
0
30
0
I’d convert everything to minutes and then do the calculation. So from memory something like this

=(Hour(F5)*60)+(Minute(F5))

Also percentage of what? What would 100% be?
Well, not percent, but decimal time I guess.

Thanks! I'll give it a go.
 

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))
 
Reactions: Bbud

lvt

Commendable
Apr 19, 2021
1,724
276
1,640
39
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
 
Reactions: Bbud
May 14, 2021
31
0
30
0
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
Mar 16, 2013
144,627
8,671
175,340
22,567
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
 
Reactions: Mandark and Bbud
May 14, 2021
31
0
30
0
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.
 

lvt

Commendable
Apr 19, 2021
1,724
276
1,640
39
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.
 
Reactions: Bbud
May 14, 2021
31
0
30
0
Not familiar with Imgur. I dumped a screenshot into a Word doc, but don't see a way to attach. I don't have a website.
 

ASK THE COMMUNITY

TRENDING THREADS