Agree with sandy. We need an uploaded Excel workbook to suggest complete solutions.

In the meantime:
When the cells are formatted, they show the correct date and time format that I am attempting to pull out information with using MID, but then the resultant pulled data is garbage. I couldnt figure out why this was happening and then I copied and pasted the original data into a new column. This revealed something odd showing that the data pastes as various numerical values around 42,000 or so. There is a pattern but I have no idea what it is. WHAT ARE THESE NUMBERS
If you are not aware of it dates and times are numbers. Dates starting at 1/1/1900 have a value of 1. All dates forward are the numbers of days since that date.
Time is a decimal fraction of a day. 8/24 or 1/3 (0.3333333....) when formatted for time is 8:00 AM.

Where I live it is currently 4/12/17 7:28 PM. The underlying number value is 43073.8111111111. That is what Excel "sees". The formatting is for human eyes.

If you format those "garbage" numbers they will display dates and times.

We still need a workbook for the rest of the question.