calculation of time difference in mid-night range

[Vishwanath Korgaonkar]

I have an issue in calculating time difference. The code works fine for all of time expect on 2 occassion
1)when the end time come between 12:00 am to 12:55 am(midnight) it gives error as shown in figure.
2)At 24 hr interval .i.e for example start time 08:00 AM ,end time 08:00AM.It should show 24hrs
My code is as follows
combobox10=start time
combobox11=end time
textbox123=time difference
Private Sub ComboBox10_Change()
Me.ComboBox10.Value = Format(Me.ComboBox10.Value, "HH:MM AM/PM")
If Me.ComboBox10.ListIndex <> -1 And Me.ComboBox11.ListIndex <> -1 Then
Me.TextBox123.Value = WorksheetFunction.Text(TimeValue(Me.ComboBox11.Value) - TimeValue(Me.ComboBox10.Value) - (Me.ComboBox11.Value < Me.ComboBox10.Value), "h:mm")

End If

need your help
thank you in advance

 

[Zot]

It is easier if you capture Date & Time together. Link discussing about this

Calculating the difference in time over midnight

Hi! I have a form that allows the user to input two times. I then need to have a calculated field that calculates how much time between the first and second time entered. The problem is, sometimes the second time entered is after midnight and therefore into the next day. Do I have to have the...

www.mrexcel.com

I guess if you just have time data only, when you want to calculate from 22:00 to 2:00 (obviously the next day) you need to add 24 to the 2:00.
Say if:
A1=22:00
A2=2:00

(A2+24) - A1 will give you 4 hour

 

[Alex Blakenburg]

Try this:-
It won't cater for > 24 hours though.

If TimeValue(Me.ComboBox11.Value) <= TimeValue(Me.ComboBox10.Value) Then
    Me.TextBox123.Value = WorksheetFunction.Text(1 + TimeValue(Me.ComboBox11.Value) - TimeValue(Me.ComboBox10.Value), "h:mm")
Else
    Me.TextBox123.Value = WorksheetFunction.Text(TimeValue(Me.ComboBox11.Value) - TimeValue(Me.ComboBox10.Value), "h:mm")
End If

 

[Vishwanath Korgaonkar]

Thanks Alex,
Code answer all my queries

 

[Vishwanath Korgaonkar]

If TimeValue(Me.ComboBox11.Value) <= TimeValue(Me.ComboBox10.Value) Then
Me.TextBox123.Value = WorksheetFunction.Text(1 + TimeValue(Me.ComboBox11.Value) - TimeValue(Me.ComboBox10.Value), "[h]:mm")
Else
Me.TextBox123.Value = WorksheetFunction.Text(TimeValue(Me.ComboBox11.Value) - TimeValue(Me.ComboBox10.Value), "[h]:mm")
End If


if you add [h] it gives up to 24hrs ie from08:00am to next day 08:30am it will give 24hrs

 

[Alex Blakenburg]

if you add [h] it gives up to 24hrs ie from08:00am to next day 08:30am it will give 24hrs

I understand what you are saying but it won't help you in this instance.

The issue is that you have a Start Time & End Time without a date, the best you can do is assume that if End Time <= to the Start Time then the End Time has clocked over in to the next day. In this case add 1 day to the End Time and calculate the duration on that basis.

If your End Time > Start Time then you have no way to determine that End Time is 1 or more days later than the Start Time.
Also for the End Time <= Start Time you have no way to determine that the End time is "more than 1 day" later than the Start Time.

This is why it has a limitation of 24 hrs.

 

[Vishwanath Korgaonkar]

Thanks Alex ,
Your code for time calculation work perfectly up to 24hrs.

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.