Calculate normal time and overtime from midnight

[Chris B via AccessMonster.com]

Hi there,

I have a form pertaining to a table with the following on it : start time
(formatted as date and time),end time(formatted as date and time), normal
hours(number) and overtime hours(number).

The shift workers to which this table pertains work on any given day until
midnight, after that their pay becomes overtime.
I am trying to create an expression that will do this,
eg,
worker works from 19H00 today until 04h00 the next day, normal hours should
be 5 and overtime should be 4
I think I would need 2 expressions, one to determine 'midnight-[start time]'
and another to work out midnight to [end time] ??

Any ideas
Thanks in advance

 

[Guest]

StartTime = #6/13/2005 19:00#)
EndTime = #6/14/2005 04:00#)
MidNight = DateSerial(Year(Endtime), Month(EndTime),Day(EndTime))
TotHrs = DateAdd("h", StartTime, EndTime)
OtHrs = DateDiff("h", MidNight, EndTime)
RegHrs = TotHrs = OtHrs

 

[LGC]

RegularHours = Round((Int(StartTime) - StartTime + 1) * 24, 5)
OvertimeHours = Round((EndTime - (Int(StartTime) + 1)) * 24, 5)

-LGC

 

[Chris B via AccessMonster.com]

Thanks for the solution, both work great!

 

[Chris B via AccessMonster.com]

Hi again,
This is working great when the start date and end dates are different, a
problem comes in when the start & end date (but different times) are the same,
it gives negatives for overtime and incorrect answers for reg hrs, although
the total hrs is correct.
1)Is there anyway to make the calulation, when the dates are the same, show
the total hrs in the regular hrs box and a zero for overtime?
2)I would like to also place a check box for overtime that would change the
expressions so that if ticked it means that all the hours worked are overtime
with a zero in regular time

Any help for the newbie is appreciated!!
Thanks Again!!

 

[Chris B via AccessMonster.com]

Hi again,
This is working great when the start date and end dates are different, a
problem comes in when the start & end date (but different times) are the same,

it gives negatives for overtime and incorrect answers for reg hrs, although
the total hrs is correct.
1)Is there anyway to make the calulation, when the dates are the same, show
the total hrs in the regular hrs box and a zero for overtime?
2)I would like to also place a check box for overtime that would change the
expressions so that if ticked it means that all the hours worked are overtime
with a zero in regular time

Any help for the newbie is appreciated!!
Thanks Again!!