1:30:00 PM | |

1:30pm | |

1:30am
| |

130p | |

1p | |

130a | |

1a | |

12pm | |

1210p | |

12a | |

<tbody>

</tbody>

Just remember, you asked for it.

Here is the formula:

=IF(ISNUMBER(A1),A1,IF(ISERROR(SEARCH(":",A1)),IF(ISERROR(SEARCH("A",A1)),IF(ISERROR(SEARCH("P",A1)),"err",IF(SEARCH("P",A1)<4,TIMEVALUE(LEFT(A1,SEARCH("P",A1)-1)&":00 PM"),IF(SEARCH("P",A1)=4,TIMEVALUE(MID(A1,1,1)&":"&MID(A1,2,2)&" PM"),TIMEVALUE(MID(A1,1,2)&":"&MID(A1,3,2)&" PM")))),IF(SEARCH("A",A1)<4,TIMEVALUE(LEFT(A1,SEARCH("A",A1)-1)&":00 AM"),IF(SEARCH("A",A1)=4,TIMEVALUE(MID(A1,1,1)&":"&MID(A1,2,2)&" AM"),TIMEVALUE(MID(A1,1,2)&":"&MID(A1,3,2)&" AM")))),IF(ISERROR(SEARCH("A",A1)),IF(ISERROR(SEARCH("P",A1)),"err",TIMEVALUE(LEFT(A1,SEARCH("P",A1)-1)&" PM")),TIMEVALUE(LEFT(A1,SEARCH("A",A1)-1)&" AM"))))