Excel export issues

[jgold723]

I'm experiencing two issues with the Export to Excel function:

1) Dates are being exported with the year in a two-character format instead of the full 4 character format, i.e. 1/1/20 instead of 1/1/2020 or 1/1/1920;

2) I have a field for zip code, but up here in Maine, our zip code begins with a "0" which is being dropped in the export

Is there a way to change these... perhaps to have these fields exported as just text?

[klimacht]

Hi, i found the solution for your problems at:

Go to Modules Select Export Manager click on the edit button of the excel entry go to export types Tab click on the edit button of the table format entry Add

 {if $field_info.data_type == "number" or substr($field_info.field_name,-3) == "num" }
        <td style='mso-number-format:General;'>
        {elseif $field_info.data_type == "date"}
        <td style='mso-number-format:"YYYY-mm-dd HH:MM"'>
        {else}
        <td style='mso-number-format:"\@";'> 
        {/if}

after the line

{assign var=value value=$submission.$col_name}

and before the line starting with

          {smart_display_field ...

Now basically all your fields except for date fields will be exported as Text Cells to Excel. If you want to have them as numbers in Excel, their field_name needs to end with "num". Adjust the datetime Template as you need or do it afterwards in excel Cheers

[jgold723]

You are my new hero! Many thanks. Works great, although one modification -- there's a cell tag (td) that lives between those two lines that needs to be deleted as well.

Otherwise, it appears to resolve the zip code issue.

The dates are coming through with all four digits in the year as well, so maybe that's also resolve. I'll test and check back in.

Thanks again!

[klimacht]

Thanks, glad it worked for you too. I had the same issue and seeing others struggle there i tried to resolve it. Actually i am really amazied by formtools and all its possibilities.

Thanks @benkeen !!!