View Full Version : Change format of Variable to Time

06-14-2012, 03:46 PM
I have a macro that gets the total time material was left out of a freezer and how many times it was taken out and displayed it as a message. It was working well until something took longer than 24 hours. Now I've gotten it to calculate correctly using [h]:mm in the spreadsheet, but cannot get it to format correctly in the message box. I have the variable declared as a string. Here is part of the macro

Set rLocation = Range("B3:B" & Range("A" & Rows.Count).End(xlUp).Row)

For Each c In rLocation
c.NumberFormat = "[h]:mm"
Next c

stLarge = WorksheetFunction.Max(rLocation)
stLarge = Format(stLarge, "[h]:mm")

'find largest number of FT
With pt.PivotFields("Sum of Frozen " & Chr(10) & "Condition")
.Function = xlCount
.Caption = "Freeze/Thaw"
End With
stFT = WorksheetFunction.Max(rLocation)
For Each c In rLocation
c.NumberFormat = "0"
Next c
Application.StatusBar = False
strResponse = MsgBox("Maximum Bench Top time is " & stLarge & vbCr & vbCr & "Maximum Number of FT Cycles is " & stFT & vbCr & vbCr & "Continue?", 4)
If strResponse = vbNo Then

Thank you!

06-14-2012, 04:03 PM
Use Application.Text(stLarge, "[h]:mm") rather than Format(stLarge, "[h]:mm")