Export Report to Excel - Date Formats do not carry through -- how to fix it?

[TAM]

My report displays the date as mm/dd/yyyy but when I choose the option to export the report to excel, the date is displayed as 21-Jan-15. How do I fix that?

[Phred]

Do the dates, after export, look correct, even though they are a different format? If so you should be able to select the range of cells in Excel with the dates and select the correct Excel format that you want. However, that would mean doing this one extra step each time you export the report. Remember the format is a "mask" and not an actual part of the data. Excel reads the field and guesses what the data is and tries to apply Excel's best guess as to format. Let me know how it goes.

Phred

[Micron]

Also, Excel refers to your regional settings when deciding what the data type is for a cell value, thus how to display it. In a new workbook, type in 3 cells 01/21/2015; 21/01/2015; 21-Jan-15 then click on each cell (one at a time) and check the cell format. You will probably find that only the last one is a date, while the others are custom or general. You control this in your regional settings, and the format you are seeing is the last in the list on my pc. I think you can control this with VBA automation or do it manually like Phred says.

[Phred]

Tam is listed as a novice so I think VB is not going to help out unless Tam has significant help. I also find it difficult to control Excel's formatting from within another program. I think manually is the only answer, as cumbersome as it might be. Anyone else have any ideas? Sorry Tam.

[Micron]

I think you can control this with VBA automation...

I may have mis-lead readers. I didn't mean control the regional settings, just the format of the data being written via automation - not an easy task for a novice for sure, as you say. However, unless one is stuck with the dd-mm-yy setting, why not change it? A 2 digit date format is a poor choice anyway, AFAIC.

[Phred]

Tam, we have not heard anything from you, so I am not going to respond any further. One other possibility exists. Does the report have to be in Excel? If not, you may have more success exporting/saving it out as a PDF. I have no experience with this but it is worth investigating.

Phred

[TAM]

Tam, we have not heard anything from you, so I am not going to respond any further. One other possibility exists. Does the report have to be in Excel? If not, you may have more success exporting/saving it out as a PDF. I have no experience with this but it is worth investigating.

Phred

Sorry - new to this forum, so wasn't sure I needed to respond back. The report does need to be in EXCEL. Until I get a bit more skilled, I'll manually update in EXCEL or create a quick macro to do that.