Extracting Time from cell and converting it to Standard Time

Hello All,

I get the following information from a report, and I need to do several things (preferably all in one formula).

What I'm given: "2016-03-02T14:54:49Z"

What I want to get to: "11:54:54 AM"

Here is what I need and why
1: Need to extract the time out of the cell

2: I need to convert it from military time to standard time

3: I need to subtract 5 hours to account for differing timezones

4: I'd like to add the text AM or PM at the end based on the actual time

Any help would be great. I'm not a novice when using complex formulas, but I've never really played with the time functions before. I'll keep working on this in the meantime.

Thanks!

Thanks! I had to adjust your formula slightly to subtract the 5 hours for the time zone difference. But otherwise great formula!

Formula:

Please Login or Register  to view this content.

Thanks!!!