Time Difference at Midnight

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is data I took over and trying to make sense of.

They were tracking the work an operator was doing on their shift for the
day, I am having a problem with the afternoon shift, their shift ends at 12
midnight so when they worked a press (#4) from 10:18 PM to 12:00 AM I get a
result of -22.30 hrs.
How do I get this number to be 1.7 hrs? Also, there are a handful that have
overtime and go to 2:00 AM.

Any help would be great!!

Thank you.
Stacey
 
Stacey,

I assume that you have StartShift and EndShift (or something like those) in
your database. If these are stored as dates using the Now() function, then
you are in good shape. If the database only stores the time portion, then
you have a little more work ahead of you. You don't indicate whether the
"overtime" is paid at a different rate, so I'll leave that to you to figure
out.

If your time fields are date/time, then you can determine the number of
minutes between the two times using the DateDiff function. The syntax for
this function is DateDiff("n", [StartShift], [EndShift]). Using the "n"
will get you the number of minutes, which you can then convert to hours. If
you use "h" it will only return the number of complete hours between the
start and end time.

Dale
 
I see Dale already responded. Here are examples you can play with in the
immediate pane.

?datediff("h", #11/15/2005 22:18#, #11/16/2005 00:00#)
2

?datediff("n", #11/15/2005 22:18#, #11/16/2005 00:00#)
102

?datediff("s", #11/15/2005 22:18#, #11/16/2005 00:00#)
6120

?(datediff("s", #11/15/2005 22:18#, #11/16/2005 00:00#) / 60 / 60)
1.7
 
This is data I took over and trying to make sense of.

They were tracking the work an operator was doing on their shift for the
day, I am having a problem with the afternoon shift, their shift ends at 12
midnight so when they worked a press (#4) from 10:18 PM to 12:00 AM I get a
result of -22.30 hrs.
How do I get this number to be 1.7 hrs? Also, there are a handful that have
overtime and go to 2:00 AM.

Any help would be great!!

Thank you.
Stacey
Click to expand...

This works for me:

DateDiff("n",#10/1/2005 10:18:00 PM#,#10/2/2005 00:00:00 AM#)/60

1.7
 
Dale,
Sorry I didn't give the fields, they are Date_Worked (Date/Time), Start_Time
(Date/Time) and End_Time (Date/Time). Apparently the times were loaded up
from an Excel spreadsheet so even when I format it to be the General Date it
only contains the time. Is there a way to combine the Date_Worked and the
Time and then I can manually change the few that stop at midnight and later -
I think that would fix my problem.
Have:
Date_Worked Start_Time End_Time
10/24/05 10:45:00 PM 12:00:00 AM

To be:
Shift_Date Start_Time End_Time
10/24/05 10/24/05 10:45:00 PM 10/24/05 12:00:00 AM
Then I can go in and update for midnight 10/24/05 to 10/25/05.

Thanks!

Dale Fye said:
Stacey,

I assume that you have StartShift and EndShift (or something like those) in
your database. If these are stored as dates using the Now() function, then
you are in good shape. If the database only stores the time portion, then
you have a little more work ahead of you. You don't indicate whether the
"overtime" is paid at a different rate, so I'll leave that to you to figure
out.

If your time fields are date/time, then you can determine the number of
minutes between the two times using the DateDiff function. The syntax for
this function is DateDiff("n", [StartShift], [EndShift]). Using the "n"
will get you the number of minutes, which you can then convert to hours. If
you use "h" it will only return the number of complete hours between the
start and end time.

Dale

SMac said:
This is data I took over and trying to make sense of.

They were tracking the work an operator was doing on their shift for the
day, I am having a problem with the afternoon shift, their shift ends at
12
midnight so when they worked a press (#4) from 10:18 PM to 12:00 AM I get
a
result of -22.30 hrs.
How do I get this number to be 1.7 hrs? Also, there are a handful that
have
overtime and go to 2:00 AM.

Any help would be great!!

Thank you.
Stacey
Click to expand...
Click to expand...
 
Stacey

You can just add the Shift_Date to the Start_Time.

ShiftStart = Shift_Date + Start_Time
ShiftEnd = Shift_Date + End_Time
If ShiftEnd < ShiftStart then ShiftEnd = ShiftEnd + 24

HTH
Dale

SMac said:
Dale,
Sorry I didn't give the fields, they are Date_Worked (Date/Time),
Start_Time
(Date/Time) and End_Time (Date/Time). Apparently the times were loaded up
from an Excel spreadsheet so even when I format it to be the General Date
it
only contains the time. Is there a way to combine the Date_Worked and the
Time and then I can manually change the few that stop at midnight and
later -
I think that would fix my problem.
Have:
Date_Worked Start_Time End_Time
10/24/05 10:45:00 PM 12:00:00 AM

To be:
Shift_Date Start_Time End_Time
10/24/05 10/24/05 10:45:00 PM 10/24/05 12:00:00 AM
Then I can go in and update for midnight 10/24/05 to 10/25/05.

Thanks!

Dale Fye said:
Stacey,

I assume that you have StartShift and EndShift (or something like those)
in
your database. If these are stored as dates using the Now() function,
then
you are in good shape. If the database only stores the time portion,
then
you have a little more work ahead of you. You don't indicate whether the
"overtime" is paid at a different rate, so I'll leave that to you to
figure
out.

If your time fields are date/time, then you can determine the number of
minutes between the two times using the DateDiff function. The syntax
for
this function is DateDiff("n", [StartShift], [EndShift]). Using the "n"
will get you the number of minutes, which you can then convert to hours.
If
you use "h" it will only return the number of complete hours between the
start and end time.

Dale

SMac said:
This is data I took over and trying to make sense of.

They were tracking the work an operator was doing on their shift for
the
day, I am having a problem with the afternoon shift, their shift ends
at
12
midnight so when they worked a press (#4) from 10:18 PM to 12:00 AM I
get
a
result of -22.30 hrs.
How do I get this number to be 1.7 hrs? Also, there are a handful that
have
overtime and go to 2:00 AM.

Any help would be great!!

Thank you.
Stacey
Click to expand...
Click to expand...
Click to expand...
 
Back
Top