Converting Dates into Downtime with an automatic calculation

[iLott]

Hi

Currently I have a form with these variables

- Assets
- Shifts
- Machine Offline Date
- Machine Offline Time
- Machine Online Date
- Machine Online Time

Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.

What I have already done is allocate shifts per asset e.g.

when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.

If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11M

also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM

This is the scenario

Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs

Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.

I would like to make this an automatic calculation, Is this possible?

 

[JHB]

..
I would like to make this an automatic calculation, Is this possible?

Yes it is.
You need a table in which you've the expected running time for each machine.
And then in a loop in which you calculate the downtime for each day.
Scenario B:
From 19:00 to 23:00 - 10/7-2015 = 4 hours
From 07:00 to 23:00 - 11/7-2015 = 16 hours
From 07:00 to 10:00 - 12/07-2015 = 3 hours

 

[iLott]

Does this expected running time need to be in a separate table or can I add it to where I have all my assets and shift count?

Also do I make 2 Columns, 1 Start the other Finish?

 

[Cronk]

You do not need to store any additional data provided the number of shifts for any asset do not change over time, nor the shift start/stop times (but see below for more).

The 3 shift operation is easy. Number of hours = ((MachineOnlineDate + MachineOnlineTime) - (MachineOfflineDate + MachineOfflineTime)) *24

For the 2 shift situation, down time calculation depends on whether the down time is across multiple days or not.

If same day, the hours is 2300 - MachineOfflineTime.

If multiple days, the hours is made up of down time on first day, last day and 16 hours for every full day in between (if any). ie 2300 - MachineOfflineTime + 16 * FullDays + MachineOnlineTime - 0700

If assets change the number of shifts, or shift times over time and you need to make calculations for a particular period, you'd need a table to record start date/ end date when a particular asset was being used for 2 or 3 shifts.

 

[JHB]

Does this expected running time need to be in a separate table or can I add it to where I have all my assets and shift count?

I a separate table, something like below, as you can see it is possible to have different start- and end-time:

 

[iLott]

You do not need to store any additional data provided the number of shifts for any asset do not change over time, nor the shift start/stop times (but see below for more).

The 3 shift operation is easy. Number of hours = ((MachineOnlineDate + MachineOnlineTime) - (MachineOfflineDate + MachineOfflineTime)) *24

For the 2 shift situation, down time calculation depends on whether the down time is across multiple days or not.

If same day, the hours is 2300 - MachineOfflineTime.

If multiple days, the hours is made up of down time on first day, last day and 16 hours for every full day in between (if any). ie 2300 - MachineOfflineTime + 16 * FullDays + MachineOnlineTime - 0700

If assets change the number of shifts, or shift times over time and you need to make calculations for a particular period, you'd need a table to record start date/ end date when a particular asset was being used for 2 or 3 shifts.

Is this achievable to do it all in 1 box? or do I have to have multiple boxes to get different calculations?

And would I need to do this in the "After Update" expression section?

 

[iLott]

I a separate table, something like below, as you can see it is possible to have different start- and end-time:

This is my current table

 

[Cronk]

"Is this achievable to do it all in 1 box? or do I have to have multiple boxes to get different calculations?"

What do you mean by boxes?

You write a function in VBA and pass the relevant values to derive the downtime.

BTW, are you recording the start/stop times for every asset or just when there is an interruption?

 

[iLott]

Firstly, To get a better understanding this is my form.


Now what I wanted is when the variables are selected.

*Asset
*Shift
*Machine Stop/Offline Date
*Machine Stop/Offline Time
*Machine Online Date
*Machine Online Time

In the marked box ("Total Downtime Hrs") I want it to auto update and calculate the total "Downtime" for that breakdown. It will provide a visual total for the managers to see how many hours their Asset was down for.

However this is only a prototype, and a wish list on their half, they do not necessarily require to know the exact downtime so if we can do a background calculation that happens in the Main table (tblMaintenance_BD_Data) and only I am able to see it, that is fine as well.

Second, I am not recording every asset start/stop times, This form will only be generated when there is an interruption.

I hope this makes sense

 

[Cronk]

Write a function based on the algorithm in my first post and set the control source of the Downtime hours text box. You don't need t0 (shouldn't) store the result of the calculation in your table.

 

[iLott]

I need the result to show in the main table, so that I am able to calculate

A: Total downtime for each department per week, month year
B: Downtime per asset
C: Cost of ea downtime.

Most of these are a background calculation.

What I need is a function to do what you have posted in the table itself not in a query as the main database gets exported into excel.

I do apologies if I am miss understanding something here.

 

[Cronk]

You don't store calculations in a table. Use a query to sum the results of calculations for the period and departments required.

If you already have "a background calculation", then that is the function, no?

What is the background calculation that you already have?

 

[iLott]

Hi Cronk

The back ground calculation is in the main "database table" labelled Tbl_MaintenanceDATA.

these calculations are derived from different parts of the column which is entered in the forms.

Example:

Man Hrs = Number of Technicians * Hour spent on breakdown
Labour cost = Man Hrs + admin hrs * (Y) value per hr
Cost per breakdown = Breakdown downtime * (X) value per hr + other cost involved, i.e. parts etc.

There are multiple calculations in the main database table like the above. This is why I wanted the breakdown downtime to be similar as well.

 

[Cronk]

IMO calculated fields in tables are not only against rules of normalisation but an abomination.

This thread is in modules and VBA. My earlier advice was to write a function to handle the calculations. If you want to use calculated fields, maybe post in the tables forum to seek guidance.

I don't intend to even start looking at calculated fields in tables. Sorry but this is where I sign out.