# Questionconverting 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.

Last edited:

#### sizzling

##### Titan
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?

Bbud

#### Eximo

##### Titan
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.

Bbud

#### Bbud

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))

Bbud

#### lvt

##### Reputable
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

Bbud

#### Bbud

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

Mandark and Bbud

#### Bbud

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

##### Reputable
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.

Bbud

#### lvt

##### Reputable
Screenshot of my example cited above :

Bbud and USAFRet

#### Bbud

Did you format your cells correctly?

The Time column must be HH:MM

The Decimal column must be Number with 2 decimals.

Yup.

#### Bbud

Can't paste a screenshot.

#### Bbud

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.

#### USAFRet

##### Titan
Moderator
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.
Right.
Toms Hardware does not host images internally.

It requires the image to be hosted elsewhere.
There is no direct 'upload' to here, no matter what file type.

Bbud

Moderator

Bbud

Thanks

#### Bbud

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.

#### lvt

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

The correct format for TIME should be 2:00 and not 2:0

Bbud

#### lvt

##### Reputable
In my example I set the "Percent" column to "Number" with 2 decimals like this

Bbud

Replies
5
Views
1K
Replies
4
Views
1K
Replies
2
Views
2K
Replies
4
Views
2K
Replies
4
Views
2K