Datediff but for Shifts

[Chad Access]

Hey everyone!

I am struggling with this one. I have events with Start Date and Stop Date. They can span hours, or even days.



I have these shifts:

1st Shift - Mon - Fri 7a-3p
2nd Shift - Mon - Thu 3p-3p and Fri 3p-7p
3rd Shfit - Mon - Thu 11p - 7a
4th Shift - Sat - Sun 7a-7p
5th Shift - Fri - Sun 7p - 7a

I need to use Datediff to find out how much time elapsed between Start Date and Stop Date, but I need it parsed out based on shift. How much of the time differential lands in 1st, 2nd, etc.

The only solutions I can think of involve tons and tons and tons of if statements, and I want to make sure there's not a simpler way before I go that route. Thanks so much for taking a look!

[Bulzie]

Be good if you can give some sample data of the dates/times in a shift and spanning shifts and then what the outcome data needs to look like.

I don't think there is an easy solution, think you would need to use code to do something like:

1. Get StartDateTime
2. Find which shift it starts in.
3. Get StopDateTime
4. Does StopDatetime Fall within the same Shift as StartDateTime, If so do the DateDiff("n", StartDateTime, StopDateTime) and add to that Shift.
5. If StopDateTime does not Fall within that shift, then do DateDiff from StartDateTime to end Datetime of that shift DateDiff("n", StartDateTime, Date() & "03:00 PM") and add to that shift. ** might calculate those end time shift values ahead of time. The one I put probably is not right **
6. Does StopdateTime fall in next shift, if so then do date Diff from Start of that shift to StopDateTime.
7. If not, then add that hold shift time to that shift.
repeat steps until StopDateTime falls within a shift.

also is 2nd shift right, u have 3p to 3p 2nd Shift - Mon - Thu 3p-3p

[Chad Access]

Thanks. This is sort of the approach I was afraid of haha. If the time spans several shifts then I have to assign 8 hours to those shifts also.

I did make a mistake, 2nd should be 3p - 11p.

[Bulzie]

So are you just getting total hours/minutes per shift for a given date range? Or you doing total per shift for each employee per date range? The coding is not that bad as once you get the logic, it will do all the work for you. You could move the results to a tblResults table by Date and Shift or Employee, Date, Shift, etc.

[Chad Access]

I would at least like total for each shift for given date range. If it were possible to generate it for each date it would be better, but that can wait for another day.

I am still a novice at the vba portion. I have seen examples of iteration, but I don't know how to make it generate a table. If you could show me the basic setup, that would be a huge help. And I assume it would just accept a start and end date parameter from a form.

[aytee111]

What do the start and stop dates look like? Do they have time with them? If not, what time does it begin?

[Chad Access]

The start time for any given date would be the start of first shift (7:00AM), and the end of any given date would be 11:00pm.

[Bulzie]

Your StartDate and StopDate include the time they clocked in right? Or is time in a separate field?

[Chad Access]

Your StartDate and StopDate include the time they clocked in right? Or is time in a separate field?

This is tracking machine downtime. These are not hours for people. The table this data comes from has a machine number, a start time, and stop time for each downtime event.

[Micron]

Very difficult to do in Access, methinks. Biggest hurdle is that you need a date field for a span and it would have to be independent of the shift date/time field. After that comes handling DownTimeDate being => that date, and UpTimeDate <= that date. Then sorting by date (but which one?) then machine and calculating the difference, assuming neither field is null. Then querying that query with a Totals query to sum the calculated field, grouped by whichever grouping you're after.

I had to do this many years ago to chart the up time of mobile equipment. I pulled Access data into Excel and used a complex cell formula to calculate the availability (up time) of each unit. 123 might go down on Monday at noon and not come back up until Thursday. In the meantime, 4 or 5 other units were going up and down so the records were date based and in order of the calendar date. Mind you, I was only calculating how many were available out of 5 or whatever the number was. So the running calculation result in a column was based on 5-1-1-1+2-1... as the dates went on. The worst thing that could happen to you (IMHO) is for you to figure this out in Access, then management asks to see a chart of it. You'd be way ahead just using the power of Excel formulae, I think. Thing is, you've said what you'd like to see, but not why and what you'd do with this info. Perhaps what I did isn't relevant because you're not after availability.

[Bulzie]

The table this data comes from has a machine number, a start time, and stop time for each downtime event.

See in your original post you said "They can span hours, or even days." So for each downtime record you have to have a StartDate, StartTime and StopDate, StopTime correct? Are they in the same field or separate?

Can you provide a sample of the fields and data from the records?

[ArviLaanemets]

Probably the easiest way to handle it will be a table, where you define start and end times (pure time, with date part 0, i.e. 01.01.1900 hh:mm) for every day of week (at Sunday or at Saturday depending on your week definition at 23:59 and at Monday or at Sunday at 00:00 you must have additional stops there too).

The table has obligatory fields DayNo, ShiftNo, StartTime, EndTime.

Then for every datetime you calculate day number and time, and then you can find out, into which shift it falls.

No idea how you will add holidays into mess though.