12:00 AM or Midnight

[gsbatch1]

RESOLVED .. 12:00 AM or Midnight

I have employees in my database who work from 3:30PM till midnight. I am using a min or max to pull the employees time in and time out for a report showing the employees and there schedules. Right now I am using 11:59PM because if I use 12:00AM, it throws the 12:00AM to the min and then pulls the 3:30PM as the max.

Is there a way to resolve this?

 

[boblarson]

You should be using the DATE and time TOGETHER and not just the time. Then it is a non-issue.

 

[missinglinq]

Most discussions on the subject will tell you that 11:59PM is not actually followed by 12:00AM and that midnight is 12:00 Midnight then 12:00:01AM.

Of course, having a Date/Time would eliminate the problem, I think.

Having spent most of my professional life in the health care field, where this kind of thing is important, I always use military time, which, to my way of thinking, is less ambiguous. 2400 hours is 2400 hours!

Linq ;0)>

Addendum: Sorry, Bob, got distracted, mid-post, by the hound needing to go out again!

People do tend to forget that the Date is part and parcel of Time.

 

[boblarson]

by the hound needing to go out again!

You need to give it a door of its own.

 

[ChipperT]

Most discussions on the subject will tell you that 11:59PM is not actually followed by 12:00AM and that midnight is 12:00 Midnight then 12:00:01AM.

Of course, having a Date/Time would eliminate the problem, I think.

Having spent most of my professional life in the health care field, where this kind of thing is important, I always use military time, which, to my way of thinking, is less ambiguous. 2400 hours is 2400 hours!

Linq ;0)>

Addendum: Sorry, Bob, got distracted, mid-post, by the hound needing to go out again!

It really doesn't matter if midnight is represented as 12:00 am, 00:00, 2400 or anything else, if you use Bob Larson's suggestion and use Date/Time instead. Otherwise, you may "fix" your problem with your 3:00 pm to midnight shift, but what do you do if someone works until 1:00 am? Using Date/Time instead will always give you the correct answer.

 

[gsbatch1]

They work Mon - Fri 3:00 PM - 12:00 AM. No dates are going to fix there schedule. If I was using a time punched in or out, then yes I would use Date/Time. I am using days worked and shifts.

 

[boblarson]

For that shift you could use

0:00 - 3:00 + 12:00

or convert to

24 - 15

So you could have a formula like:

HoursWorked:IIf([ShiftEnd] = #12:00:00 AM#, [ShiftEnd]-[ShiftStart]+12, [ShiftEnd]-[ShiftStart])

 

[gemma-the-husky]

its a inherent problem of trying to use time within access. time just IS modulo 24hrs - and there is no escape.

an alternative is to roll your own - maybe change the time into expired minutes, and use those totals for your calcs.

similar to linqs idea of using military time, i guess.


eg - what happens when you get someone working 10pm to 6am say - you will get problems every time you straddle midnight!

 

[DCrake]

In any app that I do that involves storing times only I tend to store the time as mins past midnight. Then when I see that the end mins is less than the start mins I add 1440 to the end mins and convert that back to time

So it 6pm = 1080
and 3am= 180

Duration = iif(180 < 1080, (1440+180),180) - 1080
Thus 1620-1080 = 540 mins

9 Hours

 

[gsbatch1]

So I created a Field in my query that had the following:

SchdlHrs: Format(Min([Hours]),"Medium Time") & "- " & IIf(Format(Max([Hours]),"Medium Time")="11:59 PM","12:00AM",Format(Max([Hours]),"Medium Time"))

This fixed the issue. I can use the new field in my Pivots, forms and other reports.

Right now we have no shifts that straddle Midnight, so this is my fix for now.