easiest way to convert a number to hours

[Gary Keramidas]

i am adding up some time durations and storing it in a variable. when the
variable is greater than 1, 1.384 for example, i need to format it in hours and
minutes and put the value in a cell. i keep only getting the hours greater than
24, 9:13:04, in this case.

format(variablename,"hh:mm:ss") is what i tried.

is there a way to format the number rather than doing the math to get 33:13?

 

[Gary Keramidas]

this seems to work

"[HH]:mm:ss;@"

 

[OssieMac]

Hi Gary,

I could not get your answer to work with Format function in xl2002 or
xl2007. However, the following worked in both versions:-

WorksheetFunction.Text(1.384, "[h]:mm:ss")

--
Regards,

OssieMac

this seems to work

"[HH]:mm:ss;@"

--


Gary

i am adding up some time durations and storing it in a variable. when the
variable is greater than 1, 1.384 for example, i need to format it in hours
and minutes and put the value in a cell. i keep only getting the hours greater
than 24, 9:13:04, in this case.

format(variablename,"hh:mm:ss") is what i tried.

is there a way to format the number rather than doing the math to get 33:13?

 

[Gary Keramidas]

thanks for checking.

i put 1.38 in cell and recorded the macro when i manually formatted the cell
and this is what it showed:
Selection.NumberFormat = "[h]:mm:ss;@"


the format didn't work the first time i tried it, but when i went back and tried
it again, it did. i didn't know why it changed, but since it worked, i posted
it.

but i ended up just using this:
..NumberFormat = "[h]:mm:ss"
--


Gary

Hi Gary,

I could not get your answer to work with Format function in xl2002 or
xl2007. However, the following worked in both versions:-

WorksheetFunction.Text(1.384, "[h]:mm:ss")

--
Regards,

OssieMac

this seems to work

"[HH]:mm:ss;@"

--


Gary

i am adding up some time durations and storing it in a variable. when the
variable is greater than 1, 1.384 for example, i need to format it in hours
and minutes and put the value in a cell. i keep only getting the hours
greater
than 24, 9:13:04, in this case.

format(variablename,"hh:mm:ss") is what i tried.

is there a way to format the number rather than doing the math to get
33:13?