DateDiff past Midnight

[Secret Squirrel]

I'm trying to calculate how early an employee punches out but their shift
ends after midnight. I've never had any luck with these types of time
calculations. Here's what I'm trying to do:

Fields:
EmpID
DateIn
TimeIn
DateOut
TimeOut
ScheduledEndTime

What I want to do is calculate the minutes an employee leaves early based on
the TimeOut time vs the ScheduledEndTime. The problem I'm having is sometimes
the employees leave early before midnight so I can't set up the calculation
correctly. I need to show all employees who leave early greater than 10
minutes. Can anyone help with this?

Thanks
SS

 

[Jeff Boyce]

Microsoft Access offers a date/time data time. If your fields were [In] and
[Out], you could use the DateDiff() function to find out how many (?)
minutes there were between [In] and [Out], even in the [Out] was after
midnight.

(... or am I not understanding your requirement?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Secret Squirrel]

The reason the fields are as they are is because I'm importing this data from
a spreadsheet on a nightly basis. I'm not looking to find out how many
minutes between DateIn and DateOut. I'm looking to find out if an employee
leaves earlier than their ScheduledEndTime then how many minutes early they
left. The DateIn & TimeIn are not used in this equation. How can I do that?

Microsoft Access offers a date/time data time. If your fields were [In] and
[Out], you could use the DateDiff() function to find out how many (?)
minutes there were between [In] and [Out], even in the [Out] was after
midnight.

(... or am I not understanding your requirement?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to calculate how early an employee punches out but their shift
ends after midnight. I've never had any luck with these types of time
calculations. Here's what I'm trying to do:

Fields:
EmpID
DateIn
TimeIn
DateOut
TimeOut
ScheduledEndTime

What I want to do is calculate the minutes an employee leaves early based
on
the TimeOut time vs the ScheduledEndTime. The problem I'm having is
sometimes
the employees leave early before midnight so I can't set up the
calculation
correctly. I need to show all employees who leave early greater than 10
minutes. Can anyone help with this?

Thanks
SS

 

[Secret Squirrel]

Also, the ScheduledEndTime is only a time field from the spreadsheet, not a
Date/Time field.

Microsoft Access offers a date/time data time. If your fields were [In] and
[Out], you could use the DateDiff() function to find out how many (?)
minutes there were between [In] and [Out], even in the [Out] was after
midnight.

(... or am I not understanding your requirement?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to calculate how early an employee punches out but their shift
ends after midnight. I've never had any luck with these types of time
calculations. Here's what I'm trying to do:

Fields:
EmpID
DateIn
TimeIn
DateOut
TimeOut
ScheduledEndTime

What I want to do is calculate the minutes an employee leaves early based
on
the TimeOut time vs the ScheduledEndTime. The problem I'm having is
sometimes
the employees leave early before midnight so I can't set up the
calculation
correctly. I need to show all employees who leave early greater than 10
minutes. Can anyone help with this?

Thanks
SS

 

[Mark Andrews]

Assuming ScheduledEndTime is only time (no date portion):

diffMinutes = DateDiff("n",ScheduledEndTime, TimeOut)

example1: ScheduledEndTime is 2:00am
TimeOut is 2:30am
They stayed 30 minutes past their shift (so good)
diffMinutes should be -30

example2: ScheduledEndTime is 2:00am
TimeOut is 1:45am
They left 15 minutes before their shift should end (so no good)
diffMinutes should be 15

example3: ScheduledEndTime is 2:00am
TimeOut is 11:45pm
They left 2:15 before their shift should end (so no good)
diffMinutes should be -21:45 (expressed in minutes)

so only SMALL negative numbers are good. If all you know is 2 times of the
day you have to do a cutoff
somewhere in between example 1 and example 3.
if (diffMinutes > 0) or (diffMinutes < -720) then
Msgbox "you left too early"
end if

that would make it so you could checkout up to 12 hours after your shift and
still be good.

Maybe I'm not thinking clearly, but that's my guess,
Mark
RPT Software
http://www.rptsoftware.com

 

[Secret Squirrel]

So it doesn't matter about the different date since it's before midnight?
For your example 3 how can I get the result to be 2:15 early instead of
-21:45?

 

[Douglas J. Steele]

Check http://www.mvps.org/access/datetime/date0008.htm at "The Access Web"

 

[Larry Linson]

If you are keeping this "time field from the spreadsheet" in an Access
Date/Time Variable or Field, you are not keeping just the time, but alsoo a
zero date. The zero date implies that it is that time of day on Dec. 30,
1899. With that as a starting point, perhaps it is obvious why you are
having difficulty differentiating between "before midnight" and "past
midnight". That is, if you use a time of 10:00 PM for employee's end time,
and 2:00 AM for scheduled end time, the actual values stored are:

10:00PM Dec. 30, 1899 and
2:00AM Dec. 30, 1899

thus it appears that the scheduled end time is before the employee's end
time.

That's the way it is. If you want something different, you will need to use
numeric values or apply the date, and write your own Time-Only processing
code.

Larry Linson
Microsoft Office Access MVP

Also, the ScheduledEndTime is only a time field from the spreadsheet, not
a
Date/Time field.

Microsoft Access offers a date/time data time. If your fields were [In]
and
[Out], you could use the DateDiff() function to find out how many (?)
minutes there were between [In] and [Out], even in the [Out] was after
midnight.

(... or am I not understanding your requirement?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to calculate how early an employee punches out but their
shift
ends after midnight. I've never had any luck with these types of time
calculations. Here's what I'm trying to do:

Fields:
EmpID
DateIn
TimeIn
DateOut
TimeOut
ScheduledEndTime

What I want to do is calculate the minutes an employee leaves early
based
on
the TimeOut time vs the ScheduledEndTime. The problem I'm having is
sometimes
the employees leave early before midnight so I can't set up the
calculation
correctly. I need to show all employees who leave early greater than 10
minutes. Can anyone help with this?

Thanks
SS

 

[Mark Andrews]

Look at the other post about the artical on the access web.

However I think the danger is there is no way to tell if it's left early or
left late (that's why I just choose 12 hours as the cutoff).

Mark