Time Difference at Midnight

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SMac - 22 Nov 2005 20:50 GMT

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

Reply to this Message

Dale Fye - 22 Nov 2005 21:03 GMT

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

> This is data I took over and trying to make sense of.
>
[quoted text clipped - 12 lines]
> Thank you.
> Stacey

Reply to this Message

SMac - 22 Nov 2005 21:36 GMT

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!

> Stacey,
>
[quoted text clipped - 30 lines]
> > Thank you.
> > Stacey

Reply to this Message

Dale Fye - 22 Nov 2005 22:00 GMT

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

> Dale,
> Sorry I didn't give the fields, they are Date_Worked (Date/Time),
[quoted text clipped - 60 lines]
>> > Thank you.
>> > Stacey

Reply to this Message

AnExpertNovice - 22 Nov 2005 21:22 GMT

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

Signature

My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.

Reply to this Message

fredg - 22 Nov 2005 21:28 GMT

> This is data I took over and trying to make sense of.
>
[quoted text clipped - 9 lines]
> Thank you.
> Stacey

This works for me:

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

1.7

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message