Sorting by date then by time yielding unexpected result

1

Hello!

I have a table with separate date and time columns (shown below). I’ve specified that it should sort first by date ascending and then by time ascending. Note however, that ‘6:00 PM’ is listed before ‘10:00 AM’ on 4/6. Then on 4/7, ‘3:00 PM’ is listed after ‘10:00 AM’.

Is this a bug? Perhaps the table is setup incorrectly?

Thanks for any suggestions!

Ed

2

Hey @Eduardo_Cavazos welcome to the community! Your doc isn’t public:

Screen Shot 2020-04-06 at 7.14.06 PM
Screen Shot 2020-04-06 at 7.14.06 PM838×943 63 KB

3

@Johg_Ananda

Thanks for letting me know! Let me know if it’s still unavailable.

4

@Eduardo_Cavazos great I was able to open it. I couldn’t see the formulas, but I was able to ‘Copy’ the doc and see the formulas.

Maybe you will find your answer if you make a third column, lets call it timestamp. Set it to a Date and time format and formula =[Time]

That might get you closer…

5

In order to debug this, I added the following formula column:

ToText(thisRow.Time)

As you can see, one of the times is for some reason including a date and time (see ‘Time sort 2’):

I’m guessing this is what is causing the unexpected sorting.

Also guessing that it came from cutting and pasting. :slight_smile:

Anyway, once I changed a column to ‘Time’, I kind of expected the doc to discard any underlying date.

6

Do it the way I explained in my prior answer. You will note that all times and all dates are actually 'Date and time’s.

You will learn the subtleties of working with dates and times as the computer understands it. Coda does a great job of making it very easy to work with, although its hiding some complexity that you have now surfaced.

7

@Johg_Ananda,

Ah, OK… Yes, your suggestion makes it clear that they are still ‘date and time’ values, even when specified to only render as time:

image
image1292×526 16.8 KB

Thank you for your explanation and for your patience!

Ed

8

You got it! Glad the socratic method worked here :slight_smile:

So every time you store a date or a time the computer doesn’t think of it that way. It always thinks of it as a moment in time… a date and a time. Which actually makes more sense when you really think about what a ‘time’ is as opposed to a ‘time of day’ which is how it seems you first approached it.

When you enter in a plane ‘time’ into the system, without a date, it will set it as the first possible instance of that time, which happens to be 12/30/1899.