Time overlapping In Excel version 2016

Hello my friend, see if this fits your situation.

Originally Posted by lucasar

Hello my friend, see if this fits your situation.

Not funny!

Originally Posted by RayRay10k

Not funny!

Why? It works, right?
Sorry, just tried to help.

Hi,

There probably is a formula guru out there that can condense this, but I had to use a couple of helper columns.

One column to determine time worked. The other to determine if there is an overlap in time and return the difference.

Also, are "Solid" and "Basecoats" hour overlapping? If so, I had to combine them into one set of data. See sheet "Combined"

In the "Combined" sheet, it is sorted by name and start time.

I hope this is helpful. Let me know if you have any questions.

Cheers

Originally Posted by anghicole

Will this do the work for you?

I had to add one column to manage to sum the hours, but I'll think it will fit you allright anyhow


The formula in the "Hours" column you could just keep copying down if you have more timestamps. The Sumif formula for adding the totalt hours I figured you probably know how to use so I didn't write it for all the names

Hello, Thank you so much for you assistance. This is very close to what I am looking for but not quite what I need. The hours you worked out are all correct, however my objective is to calculate precisely the amount of hours someone has spent working. In your spreadsheet the overlapping time was not deducted and all the times you calculated were added together including the overlap. I have attached your spreadsheet and made an edit to explain what I mean.

I don't think im being clear enough on my issue, so ill try examples again :D

eg.1 Power mixes 1 Job of paint from 9:10 - 11:00 am.
Power mixes ANOTHER Job of paint while mixing the first, from 9:10 - 11:00.
How long has power been mixing paint for? 110 Minutes.Which is 1,83 Hours.
The attached spreadsheet Calculates 2 mixtures done at the same time and adds them twice instead of "DETECTING" the overlap. Power has not been working 220 minutes just because the mixtures were done at the same time, he was working for 110, but completed both those mixtures within That time.

It sounds really confusing but if its not specific enough please tell me so I can try make it more clear.
This spreadsheet is on the right track and very close to what I need to reach my goal
Thank you so much for your efforts, I hope you can get back to me soon.

OK. I think I have it.

Instead of trying to identify all the different times worked on each project, I went with determining total time from very first time to the last time per person. Then subtract any gaps in time.
Hope this makes since and work for you.

Column E is the helper column that identifies gaps in the time table.

This crazy formula determines the difference between the earliest and latest time, the subtracts the gaps in time.

Please Login or Register  to view this content.

Hope this is helpful.

Cheers

This is the file with the code that I didn't remember to add earlier.

Regards,
Antonio