Jump to content
  • 0

Why wont excel properly sort timestamp?


roattw
 Share

Question

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!

caspio-date.png.70d361be8c9dbc7fc6cc249bc6c48164.png

 

caspio-cal.png.277a058bf8b6f85d3e7e67255d510db3.png

 

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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

 

image.png.bf8ceb27d190aba8a68c71938537ed56.png

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))

image.png.60927b4a1a9d2e97d1b359fce9ad3abe.png

 

 

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...