Overtime calculation

[gh444]

Hi,
A working shift is 8 hours. Anything in excess of that is overtime.

I want to display on a form overtime hours and minutes calculated as a duration in excess of eight hours. It needs to work over midnight.

I have the following to work out a shift duration:

=Format([Shift_start_time]-1-[Shift_finish_time],"Short Time")

But I can't work out how to check if it exceeds 8 hours and if so calculate the duration in excess of 8 hours.

Any help greatly appreciated.

Thanks in advance,
Graham

 

[arnelgp]

i dont know if this is correct:

=IIf([Shift_start_time]-1-[Shift_finish_time] > 8, ([Shift_start_time]-1-[Shift_finish_time])-8, 0)

 

[gh444]

Thanks but I've tried that and I'm getting a #Type! error in the form field

 

[plog]

It needs to work over midnight.

Then I certainly hope your fields are storing the date and time. If not, it's a fools errand.

If they are, then it's a simple DateDiff call: http://www.techonthenet.com/access/functions/date/datediff.php

 

[MarkK]

Your start and end values should be date AND time. Then it is a simple subtraction, keeping in mind that a date/time value is stored as a floating point value with the integer portion being the days since a fixed benchmark , namely . . .

? format(cdate(0), "dd-mm-yyyy hh:nn:ss")
30-12-1899 00:00:00

. . . and the time being a fraction of a single day. One hour is therefore . . .

? 1/24
 4.16666666666667E-02

. . . and then your shift duration is simply the date subtraction * 24 . . .

=(EndDateTime - StartDateTime) * 24

. . . which will render the hours as a decimal, or . . .

=CDate(EndDateTime - StartDateTime)

. . . which renders the hours as a date.

 

[gh444]

Thanks for the tips everyone.
Will have a rethink about the nature of my data.