I'm having a heck of a time getting Excel to recognize a formula as a time value.

I'm working with a list of college football games. The timestamp for kickoff comes in as ISO 8601. I have a formula that basically says:

- If the date/time in column E is AFTER the first Sunday in November of that year, then subtract fours hours from the time.
- If the date/time in column E is BEFORE the first Sunday in November of that year, then subtract 5 hours from the time.

(Since UTC is 4 or 5 hours ahead depending on DST)

It's giving me the correct value which is great, but even after wrapping it in TEXT(formula, "h:mm AM/PM") and formatting the column identically, it still only sorts smallest to largest. I've tried a few different options for formatting it but I'm stuck.

Thanks for any help you can give.

timeRiver.xlsx