Overlapping Time Data

[ExcelNoob625]

Hello,

I am trying to figure out how to calculate uptime and downtime from a dataset that I have. The data has a category, start time, and finish time. Some of the records overlap in time, but I only want to count one category at any given time. 

Here is the logic I want to implement:

1) Jobs count towards uptime

2) If there is downtime during a period where a job is ongoing, subtract that downtime duration from the job duration

3) If there is prep ongoing during another activity, do not count it. However, for the duration that the prep does not overlap with another activity, it should be counted as downtime.

4) If there is no data between the hours of 6:00 and 22:30, count it as category "no data" 

Here is a sample of the data:

Job	9/28/2023 6:38	9/28/2023 7:10
Prep	9/28/2023 7:10	9/28/2023 9:30
Job	9/28/2023 9:46	9/28/2023 9:57
Exchange	9/28/2023 9:57	9/28/2023 10:22
Job	9/28/2023 10:22	9/28/2023 12:09
Prep	9/28/2023 12:00	9/28/2023 12:49
Exchange	9/28/2023 12:49	9/28/2023 12:49
Job	9/28/2023 12:49	9/28/2023 13:26
Prep	9/28/2023 13:00	9/28/2023 14:25
Job	9/28/2023 14:25	9/28/2023 15:18
Prep	9/28/2023 15:19	9/28/2023 15:44
Exchange	9/28/2023 15:45	9/28/2023 16:57
Job	9/28/2023 16:57	9/28/2023 16:58
Job	9/28/2023 16:58	9/28/2023 21:19
Downtime	9/29/2023 6:42	9/29/2023 7:52
Prep	9/29/2023 7:53	9/29/2023 8:19
Exchange	9/29/2023 8:19	9/29/2023 8:19
Job	9/29/2023 8:20	9/29/2023 13:47
Downtime	9/29/2023 9:00	9/29/2023 9:30

Additionally, I would like to be able to summarize the data and view by daily, weekly, monthly.  Can this be accomplished?

[#2]

@ExcelNoob625 

To calculate uptime and downtime from your dataset and implement the described logic, you can use Excel formulas and possibly pivot tables for summarizing the data by daily, weekly, and monthly views. Here is a step-by-step guide to achieving this:

Step 1: Data Preparation Ensure your data is organized in columns with headings "Category," "Start Time," and "Finish Time." Make sure the date and time values are recognized as date/time data types in Excel.

Step 2: Calculate Uptime and Downtime You can create additional columns to calculate uptime and downtime based on your logic. Here is a general structure for the formulas you can use:

• Column 😧 Uptime (in minutes)

=IF(A2="Job", IF(C2-B2>0, (C2-B2)*1440, 0), "")

• Column E: Downtime (in minutes)

=IF(A2="Downtime", IF(C2-B2>0, (C2-B2)*1440, 0), "")

• Column F: No Data (in minutes)

=IF(AND(TEXT(B2,"hh:mm")<="06:00", TEXT(C2,"hh:mm")>="22:30"), (C2-B2)*1440, "")

Copy these formulas down to apply to your entire dataset.

Step 3: Handle Overlapping Time To handle overlapping time, you can use a more advanced formula or VBA macro, which would require a more detailed understanding of your specific data and requirements. It is essential to consider the sequence of activities and how they overlap.

Step 4: Summarize Data To summarize the data by daily, weekly, and monthly views, you can use Excel pivot tables:

1. Select your data range, including the new columns for uptime, downtime, and no data.
2. Go to the "Insert" tab and click "PivotTable."
3. In the PivotTable Field List, drag the "Category" field to the "Rows" area, "Uptime," "Downtime," and "No Data" fields to the "Values" area.
4. In the PivotTable, right-click on the "Dates" field (your timestamp column) and group it by the desired time interval (daily, weekly, or monthly).
5. Customize your PivotTable to display the data as needed.

This PivotTable will allow you to view uptime, downtime, and no data summaries by the selected time intervals.

Please note that handling overlapping time may require more complex Excel formulas or VBA coding, depending on the specific scenarios in your dataset. You may need to adjust the provided formulas to match your exact requirements. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

[#3]

@ExcelNoob625 

Tried to do that with Power Query

But that's only mock-up. Queries could be and shall be optimized. However, if we speak about months and probably years performance will be an issue. That's more job for SQL.

[#4]

Hi @SergeiBaklan,

Thank you so much for this information. This gets me pretty close to what I need. However, I have a few issues with it.

1) On the "Jobs Corrected" query, during the remove duplicates step it is removing the record with the lower duration when it should be removing the record with the higher duration (duration - downtime). These need to be ranked somehow so the correct record can be removed. I don't have Excel Online for that command.

2) The "Prep Corrected" query is counting the entire value of the prep time. It should only be counting the time where a job is NOT running. For example, below it should only count the prep time from 12:09 - 12:49 = 40 minutes not 49 minutes. I think I can use the same logic that you applied to the jobs here where you subtracted the overlap with the downtime from their durations.

Job	9/28/2023 10:22	9/28/2023 12:09
Prep	9/28/2023 12:00	9/28/2023 12:49

[#5 · Accepted solution]

@ExcelNoob625 

Hi,

1) in Job Correction - add index to fix table in memory; sort by id and duration (ascending); add another index to fix in memory again, remove duplicate

2) corrected in Prep Corrected query

Please check in attached file