Rounding Time Value Issue

[FurRobotic]

The above is an issue i'm running into. The formula I'm using in G2 is shown in H2. I cant figure out how to fix the "value" error, any help would be great.
My task is to ROUND the time in column E to the nearest hour

 

[Joe4]

That function will only work on valid time entries.
It looks like your data is entered as text, and not as date/time.
I can tell because it is left-justified, and there is no space between the seconds and "AM".
You need to fix your data for that formula to work on it.

 

[Joe4]

Or we can leave the data as-is, and use a formula to convert it to a valid time and perform your rounding on it all in one step, i.e.

=MROUND(TIMEVALUE(LEFT(E2,LEN(E2)-2) & " " & RIGHT(E2,2)),"1:00")

 

[FurRobotic]

This worked, thank you! For some reason I wasn't able to change the formatting in excel for that column

 

[Joe4]

This worked, thank you! For some reason I wasn't able to change the formatting in excel for that column

Changing the format of the cell will not change the entry from text to date.
You would still need to fix the data in the cells.

Excel does not like no spaces between the seconds and AM/PM. It will not recognize that as a time.
The quick and easy fix would be to do a Find/Replace, replacing all instances of "AM" with " AM" (note the space before "AM").
Then do the same for PM, replacing "PM" with " PM".

That should coerces all those values to be valid times then.