VBA Script Help Needed & Formula Error

See attached sheet, but what I have is a sheet that will get data pasted into upawards of 5000 rows on Sheet 1. Sheet 2 will be the results in an easier to input format. As you can see on Sheet 1, I had to do some formula to get the times extracted & then to get them to act as times. There should be no start time earlier than 0600 & no end time later than 0559. I don't know why the 0000 time is showing up as ####. That time should be 2345.

From there, the times need to be sorted into Sheet 2 in the format that I hand typed in. I need to get the Input column across the top & the time windows listed below it in alternating start/stop line ups.

If you can help, it will GREATLY appreciated as it will cut HOURS off my daily duties!