Converting a number to time with vba

[gjsala]

I have an excel cell "E2" that has a number 31.9 and I want to convert it to 31 hours 54 minutes. What line(s) of code could I use? Thanks in advance!

 

[Zathras]

Use this formula:
[blue]

  =E2/24

[/color]
and then use a custom format:
[blue]

  [h] "hours" mm "minutes"

[/color]

 

[gjsala]

Zathras,
I tried your suggestion and it gave me 7:52 am. I'm looking for it to read 31 hours and 54 minutes. Is there something else I could try?

 

[Zathras]

You could use this:
[blue]

  =INT(E2)&" hours "&ROUND((E2-INT(E2))*60,0)&" minutes"

[/color]
But you may not have understood the earlier method:

Right-click on the cell and select "Format Cells..."
Then, select "Custom" from the "Category:" list and enter the format in the box where it says "Type:" and click OK.

It works just fine in Excel 97.

 

[gjsala]

I did understand what to do in the "format cells" but in office 2K I get 7:52am. Anyway, your line of code worked great and I thank you again!!

 

[cheerio]

The first solution by Zathras was a very near miss.

Replace [h] by [hh] to get the hours to be reported in excess of 24. With the single h you get only the hours left over after the time is converted into days. (ie Excel converts 31 hours to 1 day 7 hours and gives you the 7).

You should get the 54 minutes. 52 minutes suggests you do not have exactly 0.9 of an hour but something that rounds to 0.9 when shown to one decimal place.

The modified solution works ok on Excel 2000.

Ken