gisvpn

asked on

# 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