Can't sort oldest to newest with time formula, only smallest to largest

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

Maybe I'm misunderstanding something, but when I open your file, column G sorts fine - ascending and descending.

Interesting. I see what you mean about time not sorting. That's...annoying.

I removed the TEXT() formatting, and changed the column formatting to "mm/dd/yy h:mm am/pm".

Attachment 845810

And now it sorts properly oldest to newest.

Attachment 845809

Is it me or it kind of nuts it doesn't read time and give an option to sort oldest to newest?

Thanks for help Jazz.

Attachment 845811

I don't have a "Go Advanced" option when I edit my post. But it looks like I can add them in a new post.


I removed the TEXT() formatting, and changed the column formatting to "mm/dd/yy h:mm am/pm".


FormulaUpdate_ColumnFormatting.png

And now it sorts properly oldest to newest.


ColumnSort_PostFormattingChange.png


Is it me or it kind of nuts it doesn't read time and give an option to sort oldest to newest?

Thanks for help Jazz.


jazz.jpg