Solved convert hours and minutes in excel

November 30, 2010 at 10:42:31
Specs: Windows 7

how to convert number of hours and minutes from 100 digit clock to 60 minute cloch in excel ie the employee done 6hrs50 min in 100 digit clock, I know it is 6hrs30mins in 60 digit clock (50/10*60) but I need the formula in excel PLEASE !!!

See More: convert hours and minutes in excel

Report •


#1
November 30, 2010 at 10:47:52

50/10*60 = 300 ?

try =50/100*60

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 30, 2010 at 10:57:37

this gives me only minutes but I need hrs and minutes ie in cell A1 I put 6hrs 50 mins in 100 digit clock A1 - 6.50 , but what I need is to convert 6.50 to 6.30 in cell B1 so if I have 6.50 in cell A1 I need to get 6.30 in cell B1

Report •

#3
November 30, 2010 at 11:56:33
✔ Best Answer

To get a numerical result, try:

=INT(A1)+(A1-INT(A1))/100*60

If you want a text result, try:

=LEFT(A1,FIND(".",A1)-1) & "." & VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1)))/100*60

Either way, you should study each of these formulas and keep the techniques in your back pocket for future reference.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
November 30, 2010 at 12:35:43

let me just check, seems to be fine, if yes you are the best regards from Burton

Report •

#5
November 30, 2010 at 12:48:56

Thanks for the answer, the formula is fine, you have done a great job ! Thanks again! Maggie

Report •

#6
January 7, 2011 at 19:30:59

In order to convert from HH:MM to Minutes and vice versa, in excel, you simply need to divide by 0.000694445 (for HH:MM to Min conversion) or Multiply by 0.000694445 (For Min to HH:MM conversion). The operation should be performed by selecting a cell containing the values you desire in the HH:MM (Time) format and referencing that cell in another cell and divide by 0.000694445. That target cell should then be formatted to "Number" format in the "Format Cell" options. What is displayed then on the cell is the value of HH:MM converted to Minutes. e.g:
A1 = [1:20]; A2 = [ =(A1/0.000694445)] => A2 = [80]
conversely if:
A1 = [80]; A2 = [ =(A1*0.000694445)] => A2 = [1:20]

The number 0.000694445 simply represents the internal 1 minute change within excel; any number multiplied or divided by it will convert from a standard numerical to HH:MM format and vice-versa.


Report •


Ask Question