Registrants - possibility to export fields like EVENT_DATE in datetime format?

We export registrations data for further analysis via tools like MS Excel.
Everything works fine apart from Date and Time Fields such as EVENT DATE.
These fields are exported as Text Fields ie 'Wednesday, 19th May, 2021  8:00 am' 
This means we cannot use Excel DATE and TIME Functions to extract information such day (ie Monday, Tuesday) from these fields, which would be very useful for our analysis
Is it possible to change the output of these Date and Time fields to DATETIME Format in the exported Spreadsheet(xlsx)?
I can't see anything anywhere to do this?
 

The system uses the format from Event Date Format config option. So if you want to change that format, go to Events Booking -> Configuration, change Event Date Format config option to the format you want before doing the export.

Hi Tuan,
We already have Date format set correctly but the output of excel file is still text format not datetime form. Should I set up a support ticket to send you more information?
This is NOT an urgent problem. We can live with it for the moment
Kind regards
David

Hi David

I have checked the code again and confirm that the code already used Date Format in Event Date Format config option

So in this case, I think it might happens because the office program which you are using changes the date format?

Could you change Export Data Format to CSV, then export again, and open the exported file in a text editor to see if it is in right format?

Tuan

  Hi Tuan,
I tried as you said and exported the data as CSV. I then imported into LibreOffice (which a competitor to MS Excel), Same result.
I then tried importing it into Google Sheets again same result.
I have attached a screen shot of the imported registrants file in Google Sheets and also a sample registrants list (CSV format) in zip file
I try and use the Month function to extract the month from the Event Date - The error which Google Sheets gives is #VALUE!