Conversion of time into percentage seems to be the idea.

Pages (3): 1 2 3 Next

MESEZ
Member

188

02-12-2016, 03:04 AM

I have a column of numbers in HH:mm format (2:15, 3:00, 1:45, etc). To convert 2:15 into a percent (2.25 hrs), I used the formula you provided. It works well for most cases except when hours are whole numbers like 1:0 or 2:0. I'm not sure why zeros cause issues in the calculation. The message says the MOD parameter expects numbers, but '2:0' is text and can't be converted. Any assistance would be greatly appreciated.

MESEZ

02-12-2016, 03:04 AM #1

Amegahoney
Posting Freak

789

02-19-2016, 12:48 AM

I’d convert everything to minutes and then perform the calculation. So from memory something like this
=(Hour(F5)*60)+(Minute(F5))
Also, the percentage refers to what 100% represents.

Amegahoney

02-19-2016, 12:48 AM #2

I’d convert everything to minutes and then perform the calculation. So from memory something like this
=(Hour(F5)*60)+(Minute(F5))
Also, the percentage refers to what 100% represents.

BoxelArc
Member

190

03-05-2016, 11:29 PM

I believe you're referring to Decimal time.
There are several approaches, but my preferred method in Excel is using the formula =TEXT(A1*24,"0.00").
You can also use TIME or TIMEVALUE to handle different units and formats.

BoxelArc

03-05-2016, 11:29 PM #3

Miyuumi
Senior Member

543

03-08-2016, 12:17 AM

Sure, just to clarify, you're asking about decimal time instead of percentages. Thanks! I'll try it out.

Miyuumi

03-08-2016, 12:17 AM #4

Sure, just to clarify, you're asking about decimal time instead of percentages. Thanks! I'll try it out.

GigiCakes
Senior Member

261

03-08-2016, 07:51 AM

Calculate the total hours by adding the fractional minutes and seconds to the current hour value.

GigiCakes

03-08-2016, 07:51 AM #5

Calculate the total hours by adding the fractional minutes and seconds to the current hour value.

Pug_Unicorn
Junior Member

03-09-2016, 07:45 AM

Tested with OpenOffice Calc, it functions properly for all cases:
=HOUR(A1)+MINUTE(A1)/60
A1 is the cell containing the date, for instance.
8:00 becomes 8.00
9:45 turns into 9.75
1:30 equals 1.5

Pug_Unicorn

03-09-2016, 07:45 AM #6

Tested with OpenOffice Calc, it functions properly for all cases:
=HOUR(A1)+MINUTE(A1)/60
A1 is the cell containing the date, for instance.
8:00 becomes 8.00
9:45 turns into 9.75
1:30 equals 1.5

ElasticBaconV4
Junior Member

03-13-2016, 05:31 AM

Thanks for your input.
The suggested times are set accordingly.
I’m trying to understand if the durations are being rounded correctly.
Also, I need help formatting the numbers so they represent 45.5 hours.

ElasticBaconV4

03-13-2016, 05:31 AM #7

Edo19
Member

165

03-13-2016, 07:34 AM

=30 divided by 60 gives 0.5
=45 divided by 60 gives 0.75

Edo19

03-13-2016, 07:34 AM #8

=30 divided by 60 gives 0.5
=45 divided by 60 gives 0.75

JustRhune
Member

199

03-13-2016, 08:39 PM

My entire hour values (1.0, 2.0, etc.) return the same error message shown above. The program works with all inputs except whole numbers like 1:0 or 3:0. It always gives back the previous response.

JustRhune

03-13-2016, 08:39 PM #9

My entire hour values (1.0, 2.0, etc.) return the same error message shown above. The program works with all inputs except whole numbers like 1:0 or 3:0. It always gives back the previous response.

Trentqn
Member

150

03-13-2016, 10:33 PM

#10

Are the cells formatted as requested? The Time column should display in HH:MM format, and the Decimal column needs to show numbers with two decimal places.

Trentqn

03-13-2016, 10:33 PM #10

Are the cells formatted as requested? The Time column should display in HH:MM format, and the Decimal column needs to show numbers with two decimal places.

Pages (3): 1 2 3 Next