2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Calculate a duration from times that have gaps and overlaps

I have this data of start and stop times. I want to calculate the duration of the task for each person. But overlapping times don't count. Calculating for some people is easy. If they have no overlaps, it could be the sum of the durations. For some that overlap, I could take the min start to the max end.

But there can be an arbitrary number of data points, and there can be any number of overlapping times and any number of gaps. What can I do for the duration for the more complicated ones? And of course I want one formula for the whole column. Thanks for any ideas.

All times are on the same day. They are actually datetimes, just displayed as times, so even if they were not on the same day, any math your come up with would work correctly.

Name Start End Duration

Person 1 6:39 PM 7:02 PM

Person 1 8:02 PM 8:10 PM

Person 2 6:32 PM 9:08 PM

Person 3 6:25 PM 7:02 PM

Person 3 6:32 PM 9:06 PM

Person 3 7:02 PM 8:13 PM

Person 4 7:01 PM 7:59 PM

Person 5 6:47 PM 8:43 PM

Person 5 8:43 PM 8:54 PM

Person 6 6:45 PM 9:08 PM

Person 7 7:02 PM 8:12 PM

Person 7 7:17 PM 7:20 PM

Person 8 6:56 PM 8:13 PM

Person 9 6:32 PM 8:55 PM

Person 9 6:32 PM 8:52 PM

Person 10 6:38 PM 8:55 PM

ETA: Expected output

Name Duration

Person 1 0:31

Person 2 2:36

Person 3 2:41

Person 4 0:58

Person 5 2:07

Person 6 2:23

Person 7 1:10

Person 8 1:17

Person 9 2:23

Person 10 2:17

submitted by /u/MissAnth
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#generative AI for data analysis
#natural language processing for spreadsheets
#big data management in spreadsheets
#conversational data analysis
#real-time data collaboration
#intelligent data visualization
#data visualization tools
#enterprise data management
#big data performance
#data analysis tools
#data cleaning solutions
#rows.com
#AI formula generation techniques
#Excel compatibility
#financial modeling with spreadsheets
#formula generator
#Excel alternatives
#duration
#overlapping times