Change Mo-Fri 5A-5:15 to Usable Time Format and Average Values

[Baltimorejack68]

Thank you for reading. I have a column of data that represents day and time starting at 5am and goes until midnight. It's in 15 minute increments and written in a text format. Each row has two columns of data assigned to the period. I need to manipulate the data into four columns one with the days, the next with the top of the hour (example 5:00 AM, 6:00 AM etc.) and average the data in the 3rd and 4th column to the hour it's grouped. I've been able to do this in Power Query, but ultimately that will not work for what I need. I think what I need is a VBA which is not my strong suit. Below are example of before and after.

The worksheet is attached with a before an after that might explain the situation better.

Thank you in advance for any help you can provide.