How do I calculate overnight hours in Microsoft access?

[flammie49]

I am trying to make sure that users cannot enter more downtime than there is available up-time. My calculation works just fine for day shifts but all of my graveyard people are showing negative hours. I have one date field and then a [StartTime] and an [EndTime]. Here is the formula that is working for my day shift users.

If ([EndTime] - [StartTime]) - [PlannedDwnTime] < [DowntimeTotal] Then
Cancel = True
MsgBox "Your available time cannot be less than your downtime."
End If

[jforbes]

You might try something like this:

If [EndTime] < [StartTime] Then
   If (([EndTime] + 24) - [StartTime]) - [PlannedDwnTime] < [DowntimeTotal] Then Cancel = True
Else
   If ([EndTime]  - [StartTime]) - [PlannedDwnTime] < [DowntimeTotal] Then Cancel = True
End IF
If Cancel Then MsgBox "Your available time cannot be less than your downtime."

The idea is if the EndTime is less than the Start Time, then that means the shift went over Midnight. So, add 24 hours to the EndTime and perform the check like normal. There's other ways to accomplish this, but I think this will give you the basic idea.

[flammie49]

I haven't had any luck with that yet but it has gotten me thinking about other options. What about having two calculated fields depending on whether the user is night shift or day shift? Or maybe having the user enter time as a number instead of a time. (6am would be 600) but that would probably require too much training... Still working on this problem. Not having any luck with internet searches either.

[jforbes]

OK, lets take a step backwards.

Where is the code that you have provided located?
What is the DataType of fields?

• [EndTime]
• [StartTime]
• [PlannedDwnTime]
• [DowntimeTotal]

What kind of experience would you like your users to have? And what data are you attempting to capture?

You could have them enter the Time and Date for the StartTime and EndTime, which could be somewhat complicated, or you could have them just enter a Numeric Time if you don't need to store off the Date. Or do you need only one Date, Shift Start and then the amount of time worked? You could provide your users with a ComboBox with times in 15 minute increments and have them select the closest time.

What kind of Calculations or Filtering are you planning on performing on this data you capture? What do you want to show up on your reports. Are you going to need to know the specific Dates for Start and End, or are you just going to need to know the amount of hours per shift?


An old friend used to call this Begin with End in Mind. Knowing what you want to end up with helps you figure out what you need to cover at the start.

[flammie49]

@jforbes My data types are "Date/Time" for the date as well as for the times ([StartTime] and [EndTime]). [PlannedDwnTime] and [DowntimeTotal] are both "Number" fields. I need them to enter the exact times because of other calculations. I am having them enter one date (the date they start) and then the times in separate boxes. I still haven't had luck with getting the correct minutes of [UpTime]. Thank you for your help Jforbes.

[flammie49]

@jforbes I did it! Here is my calculation. It is working for both day shift and night shift. `=(((IIf([EndTime]<[StartTime],[EndTime]-[StartTime]+1,[EndTime]-[StartTime]))*24)*60)`