Calculate time difference between machine start/stop times?

paul cooper-brunink

New Member
Joined
Jan 20, 2005
Messages
4
Hi all,

I have to find a quick way to understand for how many minutes a machine was running. The start and stop times are manually recorded on a time sheet. I need to make this electronic to give a quicker data analysis than is currently used. The time sheet has the date for the start of the shift, along with many others items (down time reasons & time, scrap qtys & reasons/time etc). The time sheet continues over the 24 hour period, covered by 3 shifts. (6am-2pm, 2pm-10pm & 10pm-6am).

As the machine is started and stopped frequently throughout a 24 hour period, i need to find the total time in mins, the machine was running for over the 3 x 480 min shifts. And assume that unless logged otherwise, the remainder is downtime. Also needed in minutes.

ie,

Machine No 1 Shift starts on 19-1-05 06:00
Mc stopped at 07:37
Mc started at 10:41
Mc stopped at 05:46 on 20-1-05

Down time logged as 07:37 to 09:21

What was the total time the mc was running? (Mins)
What was the total down time? (Mins)

What was mc running time for shift 1, 2 & 3?
What was total down time for each shift?

I will have the data arranged in columns. (Or differently if it helps)

Can any body suggest a formula, and the correct formatting for Excel 2000 that will give the desired results?

I cannot seem to get it to work so far... :oops:
PS, Yes I admit it, I'm an engineer!

Thanks for any help in advance.

Paul.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

tbird

Board Regular
Joined
Oct 21, 2002
Messages
184
Re: Calculate time difference between machine start/stop tim

You don't explain which of the machine start and stop times are am and pm. Suggest you repost with that info. There are a couple of simple formulas that will do what you want. You might also post a small extract of what your spreadsheet looks like to make it easier to give you valid answers.
 

paul cooper-brunink

New Member
Joined
Jan 20, 2005
Messages
4
Re: Calculate time difference between machine start/stop tim

Hi tbird,

The start and stop times are in the twenty four hour format. Start at 06:00 (6am 19-jan-05) and finish at 05:46 (5:46am on 20-jan-05).

I will add an extract when I get back to my mc in the office.

Paul
 

tbird

Board Regular
Joined
Oct 21, 2002
Messages
184
Re: Calculate time difference between machine start/stop tim

See the sheet below for my interpretation of your data and the key formulas in cells D:5,6,8,11 to calculate the hours. Note that the formula in D11 is different from that in D:5,6,8 as it spans the date change from 1/19 to 1/20 on the 3rd shift. Basic formula came from Chip Pearson's website.
PERSONAL.XLS
ABCDEF
1Date/Shifts1-3RunStartRunEndRunTime(min)Downtime(min)
21/19/20051242198
3
4Shift1(0600-1400)296184
56:007:3797
610:4114:00199
7Shift2(1400-2200)480 
814:0022:00480
9
10Shift3(2200-0600)46614
1122:005:46466
12
13
Sheet1
 

Forum statistics

Threads
1,148,526
Messages
5,747,211
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top