I am trying to change hh:mm:ss into a percent of one hour, using .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can't figure out how to create a formula that will change
hours:minute:seconds into a percentage, so that I can analize for how much
time in relationship to the % of an hour was used.
Example a report that is generated displays: 0:15:00 how do I write a
formula to change this number into .25, which would representant .25 of an
hour?
 
Time in Excel is actually stored as a number where the value 1 is equal to a
day, so you would need to do this:
Take the MOD of the number with 1 to make sure you only have the decimal
portion (hours of the day)
Take the resulting value and divide it by "an hour" which is 1/24th of a day
(and dividing by 1/24 is the same as multiplying by 24)
The resulting formula (assuming you have a time in A1) would be:
=MOD(A1,1)*24
To display properly, make sure the format for your formula cell is Number,
not Date or Time.
 
For A1 formatted as HH:MM:SS, then B1 (formatted as %) =Minute(A1)/60 will
give a value of 25%.

Are you only interested in the minutes?

HTH
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert Decimal Minutes to Hours-Minutes-Seconds 3
Changing hour and minutes to percentage of hours 1
Calculation of Overlap Outage Hours 17
calculate percentages of 8 hour day 2
add/sub time/hour tracking in 24 clock format 1
Formatting short times 2
HH:MM:SS 4
Per hour calculation using whole number and hh:mm:ss 1

Back
Top