X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Split Date Range Into Shifts/duration, Access 2007    
post Jul 31 2017, 06:55 AM

Posts: 2
Joined: 31-July 17

I'm building a report for machine efficiency based on time available, running time, and down time. We have a downtime tracking system that which I'm linking to, but it tracked downtime as Start date/time through End Date/Time. Down times that fall entirely within a single day/shift are easy. But I'm trying to figure out how to split one downtime record into multiple records to assign downtime by date/shift.

For Example. I've got a machine that shows 65.99 hrs of Downtime between 3/24/17 3:54:55 PM, and 3/27/17 9:54:02 AM. How can I go about converting the start and end time/dates into something like below:

3/24/17 2nd Shift 7.08hrs
3/24/17 3rd Shift 8.00hrs
3/25/17 1st shift 8.00hrs
3/25/17 2nd shift 8.00hrs

This post has been edited by Builtjeep: Jul 31 2017, 06:56 AM
Go to the top of the page
Jeff B.
post Jul 31 2017, 07:18 AM

UtterAccess VIP
Posts: 9,789
Joined: 30-April 10
From: Pacific NorthWet

First things first ...

Are your "shifts" constant/consistent? That is, are there fixed start/end times for shifts (e.g., "1st shift always starts at 8am and ends at 5pm"). If so, consider creating a lookup table holding those 'shift' start/end times.

Then, for any date/time range, you could compare the start time with the shift start/end time to get the "first shift's" portion, remove the remainder of the shift's time from the range's, then keep iterating through shifts, removing remainders until the shift end time is greater than the range's end time (taking dates into account).

I'd look into a spot of VBA code to do this.


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Jul 31 2017, 07:40 AM

Posts: 2
Joined: 31-July 17

Yes, shift times are fixed. 7am-2:59:59pm = 1st shift, 3pm-10:59:59 = 2nd shift, 11pm-6:59:59am = 3rd shift. I've already got a table created with 3 Fields, Shift name, Start_Time, and End_Time.

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th August 2017 - 12:07 AM