Date Sort isn’t working properly

[PlaidPixels]

Hi! So, I’m working with a debt doc and am sorting by date. The column is formatted as a “date” with the numeric option as shown here: https://capture.dropbox.com/TIFot7HYcWRaecnb

What I don’t understand is why the date “31" appears before the date “1” as shown here:

I’ve never seen this before so if someone can explain how to fix this where 1 is the starting point, I would be very grateful.

Thank you!

[Xyzor_Max]

@PlaidPixels, welcome to the community.

i may be wrong, but my suspicion is as follows…

this only shows the day-of-month of the date.
but if the hidden month is shown you might find that the whole date, including month, is less.

in other words, coda is sorting by the whole date. by only showing the day-of-month, you may be missing this fact.

just a thought?

max

[PlaidPixels]

Hi Max… and thanks for the welcome.

If I undertand you correctly, the numeric representation is just what’s displayed and isn’t what’s actually calculated? If this is the case, how do I not only show where a month begins with 1 but also is ordered properly, too?

[Xyzor_Max]

@PlaidPixels

The best way (IMHO) is to add a column, (lets call it DueDateDay) that contains only the day-of-month number from the DueDate column (using the formula below) and sort by THAT column.

DueDate.Day()

How this works;
Dates are stored internally as a big decimal number. The integer part is the number of days since the start of 1970. The decimal part is the time-of-day part. For most dates it is zero (midnight). For midday it is .5 and for 6pm it is .75 etc. So this internal format can store dates, times, dates & times, and durations.

So the various formats used for inputting and displaying dates and times (and durations) all convert to and from that numeric value. But even though you set the display of the DueDate column to only show the day number, it is still stored internally as the number of days since 01-Jan-1970.

This is why the sort order looks wrong, it is sorting by that numeric value which represents the WHOLE date, including the month and the year.

So we use the Day() function to extract only the day-of-month part of the date and store that in a different column. So when we sort by that column, we get the sort-order you are looking for.

Respect
Max