Time duration over midnight in decimal hours

[gh444]

Hi,

I want to calculate decimal hours duration taken for a job starting before and finishing after midnight.

I have something that works but it seems cumbersome.

Can anyone suggest a more elegant solution than the following:

Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)

Thanks

 

[essaytee]

Hi,

I want to calculate decimal hours duration taken for a job starting before and finishing after midnight.

I have something that works but it seems cumbersome.

Can anyone suggest a more elegant solution than the following:

Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)

Thanks

You could consider the DateDiff function.

 

[essaytee]

Couldn't help myself, had to check it properly.

Sample as follows (hardcoded for demo purposes).

Function demo_date() As String
    Dim d1, d2 As Date
    d1 = "22/12/2014 23:59"
    d2 = "23/12/2014 00:02"
     demo_date = "Difference in SECONDS is : " & DateDiff("s", d1, d2)
End Function

The above correctly returns 180 seconds which spans midnight.

 

[Galaxiom]

Could help myself, had to check it properly.

A couple of points here.

Function demo_date() As String

You are returning a string when an Integer or Long would make more sense.

Dim d1, d2 As Date

This line Dims d1 as a Variant and d2 as a Date.
VBA does not support multiple variable declaration in a single statement.

 d1 = "22/12/2014 23:59"
d2 = "23/12/2014 00:02"

You have fed strings to the variables. In the case of the date variable it used an implicit conversion to date using the the Regional Date Format.

The Variant was implicitly converted with the same arrangements when it was fed to DateDiff.

Moreover, string to date conversions in Windows are extrmely promiscious. If a date is invalid in the Regional Date Format (say dd/mm/yyyy) but valid in say, the US or ISO format, it will silently accept those formats instead.

Try these in the Immediate window:
? CDate("2/13/14")
? CDate("29/2/14")

The second one is my favourite. I came across it when I found a number of unexpected dates in 1929.

My point is that you should be careful and understand what happens in implicit conversions or they will bite you one day.

 

[essaytee]

A couple of points here.

Function demo_date() As String

You are returning a string when an Integer or Long would make more sense.

Dim d1, d2 As Date

This line Dims d1 as a Variant and d2 as a Date.
VBA does not support multiple variable declaration in a single statement.

 d1 = "22/12/2014 23:59"
d2 = "23/12/2014 00:02"

You have fed strings to the variables. In the case of the date variable it used an implicit conversion to date using the the Regional Date Format.

The Variant was implicitly converted with the same arrangements when it was fed to DateDiff.

Moreover, string to date conversions in Windows are extrmely promiscious. If a date is invalid in the Regional Date Format (say dd/mm/yyyy) but valid in say, the US or ISO format, it will silently accept those formats instead.

Try these in the Immediate window:
? CDate("2/13/14")
? CDate("29/2/14")

The second one is my favourite. I came across it when I found a number of unexpected dates in 1929.

My point is that you should be careful and understand what happens in implicit conversions or they will bite you one day.

Yep, everything you mentioned is correct, I was quickly typing a function (whilst the boss wasn't looking) to show the DateDiff easily and correctly accounted for midnight. The test function was originally returning a string of info. I should have changed this for demo purposes.

I agree, regional settings can play havoc with dates and I knew that would rear it's ugly head.

 

[gh444]

Thanks for the contributions everyone.

I should have mentioned that my data only has 'times' to work with.

My users didn't like to type short time format into a form and requested 'something like Outlook has', selecting times from a drop down list. I've done this with two combo boxes, one for hours (options 1 -24) and another for minutes (options at 5 minute intervals).

I originally had:

=DateDiff('n',[Job]![Start_time],[Job]![Finish_Time])\60 & Format(DateDiff('n',[Job]![Start_time],[Job]![Finish_Time]) Mod 60,"\:00")

But this did not work across midnight because the time fields have no date element.

Hence I came up with this clunky solution:

Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)

 

[essaytee]

Thanks for the contributions everyone.

I should have mentioned that my data only has 'times' to work with.

My users didn't like to type short time format into a form and requested 'something like Outlook has', selecting times from a drop down list. I've done this with two combo boxes, one for hours (options 1 -24) and another for minutes (options at 5 minute intervals).

I originally had:

=DateDiff('n',[Job]![Start_time],[Job]![Finish_Time])\60 & Format(DateDiff('n',[Job]![Start_time],[Job]![Finish_Time]) Mod 60,"\:00")

But this did not work across midnight because the time fields have no date element.

Hence I came up with this clunky solution:

Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)

For auditing purposes how then do you account for which day the hours were worked, or which day the hours of working commenced?

I understand users don't like to type in data but surely that wouldn't prevent an application from recording the data, in this case, a date. To make things easier for the user, default to today, incorporate a calendar picker and/or with some Keypress magic, incorporate "+" or "-" to increment or decrement by a day.

As regards your formula I haven't dissected it and assume it works as you say. Is there an easier or shorter version possible; I'll have to get back to you on that.

Now heading off for my Xmas dinner, oh, nearly forgot, Merry Xmas to one and all.

 

[essaytee]

Thanks for the contributions everyone.

I should have mentioned that my data only has 'times' to work with.

... snip ...

I originally had:

=DateDiff('n',[Job]![Start_time],[Job]![Finish_Time])\60 & Format(DateDiff('n',[Job]![Start_time],[Job]![Finish_Time]) Mod 60,"\:00")

But this did not work across midnight because the time fields have no date element.

Hence I came up with this clunky solution:

Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)

Okay, so you don't have the date(s) to work with, only times. You can still use the functions posted earlier.

All you have to do is add a date (any date, doesn't matter) to the times so that you now have date/time data that can be used in the DateDiff function.

The only thing you have to account for is that if the ending time is earlier than the starting time then the date for the ending time must be the next day, add that date to the ending time.

Obviously, this will only work if the hours worked are less than 24.

Is this method more convoluted than yours, I don't know, you can decide that but at least you have another option.

Now I'm off for my xmas dinner.

 

[gh444]

Thanks Steve.

Come to think about it I do have the date elsewhere on the parent form and I could bring that into play.

Alternatively as you say it doesn't really matter what date the calculation uses as jobs are always contained within an 8 hour work shift.

Hope you enjoyed your Christmas dinner!

Regards,
Graham

 

[essaytee]

Thanks Steve.

Come to think about it I do have the date elsewhere on the parent form and I could bring that into play.

Alternatively as you say it doesn't really matter what date the calculation uses as jobs are always contained within an 8 hour work shift.

Hope you enjoyed your Christmas dinner!

Regards,
Graham

I thought there'd be a date lurking around somewhere.

Yep, enjoyed the Xmas dinner, now need to up the ante a bit with my running to compensate.

Cheers,

 

[jdraw]

Here is a link to related info from Dev Ashish (mvps)

 

[essaytee]

Thanks Steve.
...snip...
Alternatively as you say it doesn't really matter what date the calculation uses as jobs are always contained within an 8 hour work shift.

...snip...

Regards,
Graham

I brought up the 24 hour thing as in my workplace, for unforeseen operations, it's possible to work the long hours. It's not a regular occurrence but it does and can happen.