Excel VBA - Dealing with Overlapping Times

I have a really big table in Excel that has multiple rows where each row contains a comparison between two employees that had worked over the same time. See examples of some sample rows in the image below:

My aim is to find out which employees had the highest income during the day but I do not want employee times to overlap. So for example, in the above sample data Employee 145 is overlapping from 17:00 to 17:30 with 3 other employees and in this case, employee 147 has the highest income out of all. So I would want only one record of employee 145 with their start time changed to 17:30 and their end time unchanged at 18:00 as they were not the highest earning employee during 17:00-17:30.

In the case of employee 150, its a little bit more complicated as they have 3 overlaps each at a different time. The result I'd want is employee 150's start time unchanged but their end time should change to 15:15 as during 15:15 to 16:00, employee 152 has the highest income.

In the case of employee 160, I'd want to first truncate their entry at 12:15 as employee 161 has much higher income from 12:15-12:45 but then I'd want to insert an additional row in the table for employee 160 having a start and end time of 12:45 and 13:00 respectively as they dont have an overlap during that period and are the highest earning employee of that time.

I appreciate that there may be more scenarios to this but my goal is to find out the highest earning employees during each time of the day without any overlaps. I’ve managed to create some code to deal with single overlaps (that can truncate, delete or add entries according to the criteria above) but I’m struggling to deal with situations when there are multiple overlaps for one employee ID or when one overlap has another overlap so I’d appreciate it if anyone has any idea of how to handle this in VBA? Pseudo code would do as well..

Many thanks in advance!

I've added Income to table A from your earlier post.

I then made a copy of the query, and edited it like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    times = List.Times(Time.FromText("00:00:00"),1440,Duration.FromText("0:01")),
    #"Changed Type2" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Start time", type time}, {"End time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "time_of_day", each times),
    #"Expanded time_of_day" = Table.ExpandListColumn(#"Added Custom", "time_of_day"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded time_of_day",{{"time_of_day", type time}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "shift", each [time_of_day] >= [Start time] and [time_of_day] <= [End time]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"shift", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([shift] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"shift"})
in
    #"Removed Columns"

Here, we're creating a list of each minute in the day (see the "times" line), then adding that as a column to expand in query A. At the step #'Expanded time_of_day', you'll see this:

Then it's just a matter of filtering the table by a flag that only retains the minute-rows that are between the start and end date of each shift:

After filtering to TRUE and removing the filter column, then loading back into the spreadsheet, you can create a pivot table by minute and have Max of Income to show the max income by minute from the beginning of the first shift to the end of the last shift.

If you prefer, grouped by hour: