I have 70 minutes which I wish to display in hours, ie 1 hour 10 minutes or 1.10. Please, how can I convert the cells from 100 to 60 to show the figures. ie 45 minutes as 45 and not 75. Thank you

✔ Best Answer

Mike, did you try your formula for values greater than 100 minutes? How about using this? It seems to work as long as the total minutes are less than 1440, i.e. less than 1 full day and there are no decimal places in the original value.

=TIME(0,A1,0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Try this: To convert 70 to 1:10

with the number 70 in cell A1

in cell B1 enter the formula:=INT(A1/100)/24+(A1-INT(A1/100)*100)/24/60

Don't forget to Format you cell B1 as Time with a 00:00 format

MIKE

It's all to do with how you enter the time format. For hours and minutes you should enter the time with the hours and minutes separated by a colon, even when the hour or minute value is zero. Example: for 70 minutes you should type 01:10

For 45 minutes you should type 00:45

If you are using a full-stop as the separator, that's why it's not displaying as you would like it to.

phil22,

Enteringthe time as you suggested makes sense, but he asked "how can I convert the cells".In other words, if the OP already has time entered/imported/calculated as minutes, then he needs a conversion method. Perhaps he already has something like this:

http://2eq9hztv2wc1k6odx469m9znq0.w...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Mike, did you try your formula for values greater than 100 minutes? How about using this? It seems to work as long as the total minutes are less than 1440, i.e. less than 1 full day and there are no decimal places in the original value.

=TIME(0,A1,0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Mike, did you try your formula for values greater than 100 minutes?No, I just did a couple of quick calculation with 60, 70 and 80.

Tried it with 100.....oops.

Like your solution better. :-)

MIKE

Thank you guys especially DerbyDad03, you have saved me a fortune in time as my whole workbook has the minutes entered in the cells as "general" so the formula has really worked magic for me and my day..... x (and I am a woman)

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History