VBA time format greater than 24 hours

[litrelord]

Hi,

I’ve got a form with some times on it and a label that shows a calculated time difference between those times as hh:mm. If the difference is greater than 24 hours it ignores the days and just tells me the hour difference e.g. 01:30 for 1 day and 1 and a half hours. If I try and use the [hh]:mm format it just shows as :12 permanently. Has anyone seem this before or know how I can get round it?

Thanks

Nick

 

[Scott Huish]

What kind of data are you putting in for the times, does it include a date? Are they in textboxes?

 

[pfarmer]

Hi,

I’ve got a form with some times on it and a label that shows a calculated time difference between those times as hh:mm. If the difference is greater than 24 hours it ignores the days and just tells me the hour difference e.g. 01:30 for 1 day and 1 and a half hours. If I try and use the [hh]:mm format it just shows as :12 permanently. Has anyone seem this before or know how I can get round it?

Thanks

Nick

Nick,

Not enough time for me to do more then post this code where I use 4 different date pickers, two for date, two for times, and then show the value between the two as hours. As you see it could be optimized, especially in the 'if' statements, but it does work. Part of this sets a minimum time of about 10 minutes. That is if you set the first date and time to a value greater then the second set, it will set the second set to the first set plus 10 minutes. This also uses the format of the date picker themselves. In any case it may give you some ideas on handling times past midnight.

Application.EnableCancelKey = xlDisabled

On Error GoTo none

DTPicker1.MinDate = Now()
DTPicker2.MinDate = DTPicker1.Value

DaysDiff = DateDiff("d", DTPicker1.Value, DTPicker2.Value)

HoursDiff = DTPicker4.Hour - DTPicker3.Hour

MinutesDiff = DTPicker4.Minute - DTPicker3.Minute
MinutesDiff = MinutesDiff / 60
MinutesDiffNum = MinutesDiff
MinutesDiff = Format(MinutesDiff, "##0.##")

JobLengthHours = DaysDiff * 24 + HoursDiff + MinutesDiff

If DaysDiff * 24 + HoursDiff + MinutesDiff <= 0.17 Then
x = DTPicker3.Minute + 10
y = DTPicker3.Hour
If x > 59 Then
x = x - 60

y = DTPicker3.Hour + 1
End If
If y > 23 Then
y = y - 24

DTPicker2.Value = DTPicker1.Value + 1

End If
DTPicker4.Minute = x
DTPicker4.Hour = y
End If

DaysDiff = DateDiff("d", DTPicker1.Value, DTPicker2.Value)
HoursDiff = DTPicker4.Hour - DTPicker3.Hour

MinutesDiff = DTPicker4.Minute - DTPicker3.Minute
MinutesDiff = MinutesDiff / 60
MinutesDiff = Format(MinutesDiff, "##0.##")

JobLengthHours = DaysDiff * 24 + HoursDiff + MinutesDiff

If HoursDiff = 1 And MinutesDiffNum > 0 Or HoursDiff >= 2 Or HoursDiff <= 0 Then Label62.Caption = JobLengthHours & " Hours"

If HoursDiff = 1 And MinutesDiffNum = 0 Or HoursDiff = -1 And MinutesDiffNum = 0 Then Label62.Caption = JobLengthHours & " Hour"

none:

Perry

 

[tusharm]

I assume you are using the Format function? That's a VB(A) function that doesn't understand 24+ hour formatting. Instead use XL's Text function that is exported to VBA through the Worksheetfunction container as in

    Dim aTime As Date
    aTime = Now() - 1 - TimeSerial(0, 30, 0)
    MsgBox Application.WorksheetFunction.Text(Range("f4").Value, "[h]:mm")

Hi,

I’ve got a form with some times on it and a label that shows a calculated time difference between those times as hh:mm. If the difference is greater than 24 hours it ignores the days and just tells me the hour difference e.g. 01:30 for 1 day and 1 and a half hours. If I try and use the [hh]:mm format it just shows as :12 permanently. Has anyone seem this before or know how I can get round it?

Thanks

Nick

 

[litrelord]

Tusharm, you hit the nail on the head there, it's now working perfectly.

Thank you to everyone who responded.

 

[tusharm]

You are welcome. Glad to be of help.