Community
Showing results for 
Search instead for 
Do you mean 
Reply

Date formatting when exporting to excel

Accepted Solution Solved
New Member
Posts: 3
Country: Canada
Accepted Solution

Date formatting when exporting to excel

I currently am using Act Premium Web resource 17.1.156.0 on Expoler 9 Office 2010 windows 7

 

once i export my data to excel I would like to sort is by date, but it seems the excel does not recognize the date as a date, but as a vaule or text. I have tried reformatting my date on my computer and in Act, but with no success.

 

any suggestions


Accepted Solutions
Solution
Accepted by Gary W (Administrator)
‎12-15-2015 01:22 AM
Administrator
Posts: 4,024
Country: United_Kingdom

Re: Date formatting when exporting to excel

[ Edited ]

This is intended behaviour, due to the way SQL and Excel interpret some dates. It exports in this way to ensure the correct date format is maintained during the export.

 

You can work around this by:

  1. Selecting only the column of "dates"
  2. Click Data > Text to Columns
  3. Click Next
  4. Click Next
  5. In step 3 of the wizard, check "Date" under Col data format, then choose: "MDY" from the drop down.
  6. Click Finish

You can then use excel's date formatting tools on this column to edit the format to your liking.

View solution in original post


All Replies
Solution
Accepted by Gary W (Administrator)
‎12-15-2015 01:22 AM
Administrator
Posts: 4,024
Country: United_Kingdom

Re: Date formatting when exporting to excel

[ Edited ]

This is intended behaviour, due to the way SQL and Excel interpret some dates. It exports in this way to ensure the correct date format is maintained during the export.

 

You can work around this by:

  1. Selecting only the column of "dates"
  2. Click Data > Text to Columns
  3. Click Next
  4. Click Next
  5. In step 3 of the wizard, check "Date" under Col data format, then choose: "MDY" from the drop down.
  6. Click Finish

You can then use excel's date formatting tools on this column to edit the format to your liking.

New Member
Posts: 3
Country: Canada

Re: Date formatting when exporting to excel

that is awesome! thanks so much!