MsgBox to display [hh]:mm

[learner]

Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as [hh]:mm
and is displayed right. But when I try to use the value in the message box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you

 

[Tom Hutchins]

Try this

wtime = ActiveCell.Value
MsgBox ("You worked for " & Format(wtime, "hh:mm") & " Hrs:Min")

The cell's format isn't part of the value you are storing in wtime, so you
have to format it in the messagebox also.

Hope this helps,

Hutch

 

[Per Jessen]

Hi

You have to format the timevalue:

wtime = ActiveCell.Value
MsgBox ("You worked for " & Format(wtime, "hh:mm") & " Hrs:Min")

Regards,
Per

 

[Mike H]

Hi,

Try this

hrs = WorksheetFunction.Text(ActiveCell.Value, "[hh]") & " Hours "
Mins = Minute(ActiveCell.Value) & " Mins"

MsgBox "You worked for " & hrs & Mins

Mike

 

[learner]

Thanks Tom, works perfect.

Try this

wtime = ActiveCell.Value
MsgBox ("You worked for " & Format(wtime, "hh:mm") & " Hrs:Min")

The cell's format isn't part of the value you are storing in wtime, so you
have to format it in the messagebox also.

Hope this helps,

Hutch

Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as [hh]:mm
and is displayed right. But when I try to use the value in the message box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you

 

[learner]

I appreciate Mike,

It worked too and it now looks better than what I had in mind.

Hi,

Try this

hrs = WorksheetFunction.Text(ActiveCell.Value, "[hh]") & " Hours "
Mins = Minute(ActiveCell.Value) & " Mins"

MsgBox "You worked for " & hrs & Mins

Mike

Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as [hh]:mm
and is displayed right. But when I try to use the value in the message box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you

 

[Rick Rothstein]

You will also want to use Mike's WorksheetFunction.Text method instead of
the Format function that others posted **if** there is any chance that the
total hours could be more than 24... the Format function does not support
the [hh] argument (that is, you can't use the square brackets with it) and
will "wrap" the hours back to 0 at the 24-hour mark.

--
Rick (MVP - Excel)

I appreciate Mike,

It worked too and it now looks better than what I had in mind.

Hi,

Try this

hrs = WorksheetFunction.Text(ActiveCell.Value, "[hh]") & " Hours "
Mins = Minute(ActiveCell.Value) & " Mins"

MsgBox "You worked for " & hrs & Mins

Mike

Trying to finish up the macro with this message box

wtime=ActiveCell.Value
MsgBox("You worked for " & wtime & "Hrs:Min")

Cell where the value is taken from contains formula, is formatted as
[hh]:mm
and is displayed right. But when I try to use the value in the message
box, I
get something like 9.12435E-3.

It's gotta be simple but I can't figure it out.
Thank you