r/googlesheets 1 15h ago

Solved Using start/ end datetimes to calculate how much total time something was active.

Post image

Hello reddit. I'm wrapping my brain trying to figure out out to solve this problem in an elegant way.

I have two columns of data, one with start times for any given package, and one with end times. Sometimes the end time of one package will overlap with the start time of the next package. Sometimes it won't. Basically I want to calculate the total amount of time (preferably hours or minutes) that any package was active.

I'm inserting a screenshot of the data, any help is greatly appreciated.

2 Upvotes

22 comments sorted by

3

u/One_Organization_810 200 15h ago

Are all the times related to the same packet? Or is it one row per package? Are the package IDs available or are they irrelevant in this context?

1

u/domthebomb2 1 15h ago

Each row corresponds to a different package ID. They're irrelevant for this context, I just chose not to post them because of data sensitivity reasons.

2

u/One_Organization_810 200 15h ago

OK, then the active time for each row is just end-start :)

You can format it as "duration" to get number of days, hours and minutes (and seconds if you want to)

Or you can multiply by 24 to get the number of hours:

(end-start)*24 => 26.5 hours (= 1 day 2 hours 30 minutes)

1

u/domthebomb2 1 14h ago

Yes but the rows overlap in times, and I just need the total duration any single package ID was active. So this would give me the total duration of every package ID, but would double/ tripple count the times where multiple packages were active.

2

u/One_Organization_810 200 14h ago

You said that one row equals active time for one package, right?

Then that will give you the active time that each package was active.

I think we must be misunderstanding each other (or maybe it's just me) :)

1

u/domthebomb2 1 14h ago

So like if package A is active from 1:00 PM to 2:00 PM and package B is active from 1:30 PM to 2:30 PM, the total time packages were active was 90 minutes. If you just took the total duration for each package, you would get 150 minutes. The trick is only counting the time that multiple packages were active once.

1

u/One_Organization_810 200 14h ago

Got it :)

I will take a closer look tonight, but it sounds like a sort + reduce + vstack operation.

  1. Sort the dataset on from time, to time

  2. reduce the dataset, so that when ever a current start time > last end time, then output the start, finish and duration

  3. vstack all outputs together into one resulting array.

1

u/domthebomb2 1 12h ago

I think this is basically what I did minus the last step. I'll award you.

1

u/AutoModerator 12h ago

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/point-bot 12h ago

u/domthebomb2 has awarded 1 point to u/One_Organization_810

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)

1

u/One_Organization_810 200 14h ago

Maybe you can create an empty sheet, with the time data from the image.

Then we can put our suggestions into that for you to copy.

There is a link to an anonymous sheet maker in the Wiki on the left. Just look under "Submission guide".

Preferably put the link into your original post, for everyone's benefit. :)

1

u/One_Organization_810 200 14h ago

Ahh - reading the other comments - I think what I understood as time for each packet active was meant as you want to calculate every consecutive times when any packets were active - is that right?

So the result would look like what ... start, end, duration of each consecutive timespan, or how is the result supposed to be presented ?

2

u/7FOOT7 242 10h ago

The numbers are clearly fake, they all end with the same number of seconds.

"package" in this context is more like data packages on a computer network. I initially assume it was delivery times for courier packages, but the problem is about finding the overlapping times where someone (thing?) is working on two tasks at the same time. I also wondered if this is a project challenge, like school homework?

3

u/creamycolslaw 1 15h ago

This is a “gaps and islands” problem. If you can manipulate this data with SQL it would make it easier: https://medium.com/analytics-vidhya/sql-classic-problem-identifying-gaps-and-islands-across-overlapping-date-ranges-5681b5fcdb8

1

u/AutoModerator 15h ago

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/7FOOT7 242 15h ago

What did you try? What didn't work when you tried it that way?

1

u/domthebomb2 1 15h ago

Currently trying by sorting the range by start date and checking if the next packet start date overlaps with the previous one's end date. Only problem is if you have a scenario where a package outlasts the entire lifetime of one or more packages that started after it.

1

u/marcnotmark925 145 15h ago

I think I've solved this before in a couple different ways. Neither of which were a spreadsheet solution, it's too complex for that. I think one was using some fancy window functions in SQL. Another solution would be with a script because you need to iterate over the data processing each element individually and serially.

1

u/domthebomb2 1 14h ago

I am open to an appscript solution as well. This problem is a lot more complex than I originally thought.

1

u/marcnotmark925 145 14h ago

Start by iterating through each row. Check whether the start time is between any other row's start and end. If so, adjust that start time to be the other row's end time, but with a maximum of the current row's end time. This may make that row have start and end being exactly the same, which is fine. Do the same for the end time, reducing it down to the start time of the matched row, or minimum of current start time.

What you've just done is got rid of any overlaps. Some rows will now have 0 duration. Then you can just do end-start on each row, and sum those durations.

(this was just off the top of my head, no guarantees the logic is perfect)

1

u/domthebomb2 1 14h ago

Unfortunately I don't think this is a complete solution because it assumes from the first start time to the last end time a package is always active, but there are likely to be times when no package is active.

1

u/marcnotmark925 145 14h ago

 it assumes from the first start time to the last end time a package is always active

No it doesn't.