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

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

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 thisHow-To.

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

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

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.

Ask Your Question

Weekly Poll

How concerned are you about the OpenSSL "Heartbleed" bug?

Discuss in The Lounge

Poll History