Why wont excel properly sort timestamp?

[roattw]

Greetings all.

I have a DP and table that has both a timestamp field for when a record created and on the DP a text/calendar popup field user selects date they make the entry.

When I export this data to Excel, excel never sorts it correctly, even after making sure its set to a date format.

On the DP I have the calendar popup field set to  the On Load receive to [cbtimestamp*] so it defaults to that day.  Possibly not the best approach?

Regardless, when exported to Excel those columns never sort correctly by date:

What am I missing?  Thank you, as always!

 

 

[Wikiwi]

From what I understand, when you convert your table to a an excel file the formatting for each column is in a "general" format. What you can do is to highlight that column in excel and change the format to date and sort it again.

[ChristianM]

When exporting as Excel, date/time fields are exported as "Text" which is why they sort according to "Text" not their actual value.

 

You will need to add a second column in Excel to convert the "Text" date to a value Excel can interpret as a Date/Time.

I think I tested enough string length combinations for this code to make that conversion:

=DATEVALUE(LEFT(yourcell,FIND(" ",yourcell,1)))+TIMEVALUE(MID(yourcell,FIND(" ",yourcell,1),11))

 

 

[roattw]

I did something similar to ChristianM's solution.  In Excel is split time/date cell (12/31/2022 11:58:46 PM) into 2 columns - leaving just date12/31/2022 in first column and 11:58:46 PM in new column.  Then I just deleted the time column altogether.

THEN applied  =DATEVALUE(cell range) to the dates.

[ShamsMalik]

dates in Excel are actually integer numbers starting from 01 Jan, 1990 as 1. For example 17 Mar 2021 is actually 44272. Applying formats like yyyy-mm-dd, yyyy-mm, or yyyy you only change visual representation of such numbers, it shall not affect sorting.

[IamNatoyThatLovesYou]

Hello Everyone, 

 Just want to inform you that Excel files with .xls extensions will no longer be supported. More information can be seen in the link below.

- https://howto.caspio.com/deprecations/deprecation-of-xls-format-for-data-import/

 

-Natoy