Time calculations across midnight

[crookesa]

I'm dealing with times in Excel with no date. The times refer to the start and end of a workers duty.
I'm trying to find the different between the 2 times to ensure a worker has enough time between 2 duties.

Sub test_time_diff()
Dim start_time As Date
Dim end_time As Date

today_duty_value = "F"
yesterday_duty_value = "V"

'column 2 is the start time and column 3 is the end time of duties
start_time = Format(Format(Application.VLookup(today_duty_value, range("duty_times"), 2, False), "hh:mm"))
'start_time is now 06:30:00
end_time = Format(Format(Application.VLookup(yesterday_duty_value, range("duty_times"), 3, False), "hh:mm"))
'end_time is now 22:30:00

Debug.Print Format(start_time - end_time, "hh:mm")

End Sub

The results of this gives me 16:00 when in fact it's only 08:00.

I also want to be able to subtract times from midnight, so for example if they start at 00:00 and I subtract 2 hours from midnight I want the result to be 22:00
I had tried adding times that cross midnight (or from midnight) and those additions appear to work as expected.

I use a VLOOKUP to find the corresponding start and end time of a duty code in a duty table. The roster is in a separate range of workers names as rows and columns as the day of the month with the intersecting cells populated with a duty code.

 

[jasonb75]

See if this works, MOD(end-start,1) works in a formula but not tried it this way in vba. The native vba Mod command only works with integers, not decimals.

Debug.Print Format(Application.Mod(end_time - start_time ,1), "hh:mm")

 

[crookesa]

See if this works, MOD(end-start,1) works in a formula but not tried it this way in vba. The native vba Mod command only works with integers, not decimals.

Debug.Print Format(Application.Mod(end_time - start_time ,1), "hh:mm")

Getting a run-time error '438'

 

[jasonb75]

This one should work

Debug.Print Format(Evaluate("=Mod(" & end_time - start_time & ",1)"), "hh:mm")

 

[crookesa]

Debug.Print Format(Evaluate("=Mod(" & end_time - start_time & ",1)"), "hh:mm")

Just tried it there and had to switch the time variables around, so the following seems to work.

Debug.Print Format(Evaluate("=Mod(" & start_time - end_time & ",1)"), "hh:mm")

I'll have a play around with different scenarios over the next while and see if something doesn't work or try other combinations.

@jasonb75 You might explain to me if possible why i need to use the MOD function.

 

[jasonb75]

had to switch the time variables around

It's your earlier definitions that are wrong, your start_time should be yesterday and end_time should be today.

why i need to use the MOD function.

Time differences that span midnight return a negative value, MOD with a negative result and a positive divisor gives you the difference between the divisor and the absolute value of the difference.
The 1 at the end of MOD signifies 1 day (24 hours).

I'll have a play around with different scenarios over the next while and see if something doesn't work or try other combinations.

Unless you have time differences over 24 hours then it will work fine, =MOD(end_time - start_time,1) is a tried and tested excel formula that has been used by many people for a long time.
Using Evaluate in vba means that it works exactly the same as a normal formula.

I suspect that the first method I suggested merely didn't work because vba has a mod function, so the formula version is not valid when used that way. The vba mod function doesn't work with times, which is why I didn't suggest it.