Find out time doing nothing in start/End Time columns¬
Dear EE,
I have a challenge. I have two columns of time/dates which are essentially start and end dates of when a piece of machiney was working - dates and times overlap as the machine could do more than one thing at a time.
I would like to work out (by which ever means best) the effective time the machine was actually doing something, minus the time it was sitting idle.
As an example I have the below data :
Column A - Start Column B -End
30/01/2013 01:02 30/01/2013 01:02
30/01/2013 09:01 30/01/2013 09:01
30/01/2013 17:03 30/01/2013 17:03
30/01/2013 19:37 30/01/2013 19:37
30/01/2013 22:00 30/01/2013 22:02
30/01/2013 22:00 30/01/2013 22:12
30/01/2013 22:00 30/01/2013 22:12
30/01/2013 22:00 30/01/2013 22:09
30/01/2013 22:00 30/01/2013 22:11
30/01/2013 22:02 30/01/2013 22:03
30/01/2013 22:04 30/01/2013 22:05
The first date and time is 30/01/2013 01:02 and the last is 30/01/2013 22:12 - essentially a duration of 21 hours and 10 minutes. But I would like to remove the total time when the machine was doing nothing. I worked out (I think!) that the machine was actually only working for a total of 12 minutes.
Is there a way to easily approach this with Excel - I am open to any solutions best for something like this - VBA would be great or formula?
Many thanks,
Regards,
GISVPN
I have a challenge. I have two columns of time/dates which are essentially start and end dates of when a piece of machiney was working - dates and times overlap as the machine could do more than one thing at a time.
I would like to work out (by which ever means best) the effective time the machine was actually doing something, minus the time it was sitting idle.
As an example I have the below data :
Column A - Start Column B -End
30/01/2013 01:02 30/01/2013 01:02
30/01/2013 09:01 30/01/2013 09:01
30/01/2013 17:03 30/01/2013 17:03
30/01/2013 19:37 30/01/2013 19:37
30/01/2013 22:00 30/01/2013 22:02
30/01/2013 22:00 30/01/2013 22:12
30/01/2013 22:00 30/01/2013 22:12
30/01/2013 22:00 30/01/2013 22:09
30/01/2013 22:00 30/01/2013 22:11
30/01/2013 22:02 30/01/2013 22:03
30/01/2013 22:04 30/01/2013 22:05
The first date and time is 30/01/2013 01:02 and the last is 30/01/2013 22:12 - essentially a duration of 21 hours and 10 minutes. But I would like to remove the total time when the machine was doing nothing. I worked out (I think!) that the machine was actually only working for a total of 12 minutes.
Is there a way to easily approach this with Excel - I am open to any solutions best for something like this - VBA would be great or formula?
Many thanks,
Regards,
GISVPN
ASKER
Hi helpfinder,
Yes I would like to do something slightly different. I would like to know the start and end times but I am not worried about overlaps - i.e. when the machine was doing two things at once. for example :
30/01/2013 01:00 30/01/2013 01:20
30/01/2013 01:00 30/01/2013 01:20
30/01/2013 17:00 30/01/2013 17:40
If we used the above I would like to have the result as 1 hour as the first two were running at the same time for the same duration. So instead of reporting this as 1 hour 20 minutes I would like to see it as 1 hour only (40+20).
No so easy!
Regards,
GISVPN
Yes I would like to do something slightly different. I would like to know the start and end times but I am not worried about overlaps - i.e. when the machine was doing two things at once. for example :
30/01/2013 01:00 30/01/2013 01:20
30/01/2013 01:00 30/01/2013 01:20
30/01/2013 17:00 30/01/2013 17:40
If we used the above I would like to have the result as 1 hour as the first two were running at the same time for the same duration. So instead of reporting this as 1 hour 20 minutes I would like to see it as 1 hour only (40+20).
No so easy!
Regards,
GISVPN
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
hi scsyme,
Thanks for the post this looks perfect - thanks for the solution!
Regards and much appreciated,
GISVPN
Thanks for the post this looks perfect - thanks for the solution!
Regards and much appreciated,
GISVPN
You're very welcome.
Did you manage to apply it to work with your data?
ASKER
Hi scsyme,
It was all going ok, but this data didnt seem to return a correct answer, this was higher than expected - see attached, was there something not right in here?
Q28036870---v2.xlsx
It was all going ok, but this data didnt seem to return a correct answer, this was higher than expected - see attached, was there something not right in here?
Q28036870---v2.xlsx
Hi gisvpn,
The answer given is correct. Look at row 10: this one row accounts for 526 of the 527 reported minutes. The final minute resulting from the earlier start time of the previous four rows, which all overlap with row 10.
I hope this makes sense.
The answer given is correct. Look at row 10: this one row accounts for 526 of the 527 reported minutes. The final minute resulting from the earlier start time of the previous four rows, which all overlap with row 10.
I hope this makes sense.
ASKER
Hi scsyme,
I think you're right, I think the problem was there was a minute difference and I was not sure where this was coming from, I think it is just a ronuding things.
Many thanks,
GISVPN
I think you're right, I think the problem was there was a minute difference and I was not sure where this was coming from, I think it is just a ronuding things.
Many thanks,
GISVPN
ASKER
Excellent!
see my sample
sample.xlsx