Excel VBA - displaying total calculated hours in a MsgBox? 1

[knifey]

Hi,
I have a sub that iterates over worksheets adding up times (double data types). Then displays the total hours in a cell formatted as [h]:mm
My problem is I need to display this in a MsgBox instead of a formatted cell. I've tried variations of:
MsgBox ("Total hours available for this date is " & Format(actualRemainingNurseHours, "[h]:mm"))
...and...
MsgBox ("Total hours available for this date is " & Format(actualRemainingNurseHours, "hh:mm"))
The first MsgBox displays :01 and the second displays 12:00 (instead of 60:00).
Can anyone advise the correct syntax to use to get this displaying correctly?
Any pointers would be much appreciated.
Thanks,
K

 

[SkipVought]

hi,

The VB Format() function does not have the same capability as [h] cell formatting.

Try the TEXT() worksheet function...

MsgBox ("Total hours available for this date is " & Application.Text(actualRemainingNurseHours, "[h]:mm"))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I notice that your variable is actualRemainingNurseHours

Your DURATIONS Units need to be DAYS -- simple mathematical difference between 2 Date/Time values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If actualRemainingNurseHours is a named range then

msgbox actualRemainingNurseHours.text will show the actual displayed text in the cell
So MsgBox ("Total hours available for this date is " & actualRemainingNurseHours.text )
should do the trick

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[knifey]

Thanks Skip, It works like a charm.
K