Solved Display Minutes as hours in Excel

July 23, 2013 at 08:05:12
Specs: Windows 7
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

See More: Display Minutes as hours in Excel

Report •


✔ Best Answer
July 23, 2013 at 09:59:33
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.



#1
July 23, 2013 at 09:16:30
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

http://www.skeptic.com/


Report •

#2
July 23, 2013 at 09:30:52
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.


Report •

#3
July 23, 2013 at 09:49:23
phil22,

Entering the 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.


Report •

Related Solutions

#4
July 23, 2013 at 09:59:33
✔ 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.


Report •

#5
July 23, 2013 at 10:06:37
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

http://www.skeptic.com/


Report •

#6
July 24, 2013 at 01:13:29
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)

Report •

Ask Question