Exports and Date Formatting Problems with Excel

Reviving this thread.  Is there anything that is being done to address?  I randomly have some dates that will accept a British time in excel and others that won’t.

Hi @mstruening - we don’t have any current plans to address this at the moment. Thanks for sharing your feedback - I appreciate you ‘reviving’ the idea so that we know it’s still a relevant problem.

This is not just an excel problem interpreting the fields, inSided is exporting data in the CSV with random date formats. You can see it if you open it in a raw text editor like notepad.

vs

Surely this is more a bug than idea

Hey @dandre - thanks for sharing this. I couldn’t reproduce that behaviour on OSX opening a few export files with sublime text - can you confirm which export this is from (e.g. topic or post export)?

Hey @daniel.boon - this was specifically from the forum posts export for a specific date range.

If not reproducible I can open a support case and we can work through it, sharing the exports we are seeing.

Commenting here to say, +1. These date formatting issues are a huge pain in the butt - it would be amazing if users could designate the format they prefer, or at the very least have the format consistent throughout the export. Re-sparking this thread! 

I agree - I remember dealing with this a few times in the past and eventually giving up.

Now I’m trying to identify “inactive users” and would like to sort based on their join date, last activity, last login, etc… but it’s turning out to be a very tedious task because I can’t format the any of the dates that are exported as “text” (which is about half).

PLEASE PLEASE look into fixing this bug!

Couldn't get the other workarounds mentioned to work for me, so here's what I did in Excel (also works in Google Sheets, I think).

For working with original data values in column A (mix of strings + date values):

1. Create column B to convert date values to integers --- Formula: INT(A2) --- (date values will be converted, strings will show #VALUE! error)
2. Create column C to convert to text strings to date values --- Formula: =DATEVALUE((MID(A2,4,2)) & "/" & (LEFT(A2,2)) & "/" & (MID(A2,7,4))) --- (date values will show error, strings will become unformatted serial date values)
3. Remove Error cells with Find and Replace --- Replace #VALUE! or #ERROR! with nothing
4. Create column D as SUM of cleaned data from B+C --- Formula: =SUM(B2,C2)

Now you can format and sort your dates using values in column D.

Oh good I’m not the only one with this issue. I’ve been looking up ways to reformat in Excel and nothing is working. Thanks to everyone who has posted a workaround. 

This has probably already been touched on, but the dates aren’t even consistent in the same export. For example, this is an entirely un-edited doc:

One export uses multiple date formats. When I try to use Excel’s built-in formatter, it does not recognize the “dd-mm-yyyy hh:mm:ss” format as a date at all. I can’t use the INT formula to separate it, I can’t use the date formatter to update it. The date formatter doesn’t even work if I change the system language. All I want to do is filter by year joined but I have to spend multiple hours fixing the data I receive before I can do that. What a pain. 

I have considered an API workaround btw but with over 25,000 registered users, it would take quite awhile to perform the checks that I could do in minutes within Excel. I’m surprised this isn’t going to be resolved. As a US-based user, it’s pretty disappointing. 

Any update on this idea?