Constituent IDs converting to date in Excel...HELP

Julie Hiland · February 2015

I inherited a database that uses hyphenated constituent ID's based on the year they were generated. So when I create import file or export to Excel, either in .XL, .XLS, or .CSV format, Excel wants to switch my ID's to a Date format. And once they're date-formatted in Excel, there's no way to re-format to get the original constituent ID. HELP. This is having a huge impact on my ability to function, to run imports, etc. Has anyone had this problem before?

Anonymous Blackbaud User · February 2015

Julie Hiland:
I inherited a database that uses hyphenated constituent ID's based on the year they were generated. So when I create import file or export to Excel, either in .XL, .XLS, or .CSV format, Excel wants to switch my ID's to a Date format. And once they're date-formatted in Excel, there's no way to re-format to get the original constituent ID. HELP. This is having a huge impact on my ability to function, to run imports, etc. Has anyone had this problem before?

Have you tried exporting to text? Not sure if that would make any difference from a csv when opening it in Excel.

Mary McConne · February 2015

Julie Hiland:
I inherited a database that uses hyphenated constituent ID's based on the year they were generated. So when I create import file or export to Excel, either in .XL, .XLS, or .CSV format, Excel wants to switch my ID's to a Date format. And once they're date-formatted in Excel, there's no way to re-format to get the original constituent ID. HELP. This is having a huge impact on my ability to function, to run imports, etc. Has anyone had this problem before?

What a cute way to try and keep track of how many constituents were added in a year

. When you say excel turns it into a date format do you mean mmddyy? I did some googling and found this: http://superuser.com/questions/509963/stop-excel-from-converting-copy-pasted-number-text-values-to-date. There are several answers here. I'm thinking the one that has a list of 11 steps might do the trick. Long term solution is it possible to globally change the Constituent ids to remove the hyphen?

Julie Hiland · February 2015

Mary McConnel:
What a cute way to try and keep track of how many constituents were added in a year . When you say excel turns it into a date format do you mean mmddyy? I did some googling and found this: http://superuser.com/questions/509963/stop-excel-from-converting-copy-pasted-number-text-values-to-date. There are several answers here. I'm thinking the one that has a list of 11 steps might do the trick. Long term solution is it possible to globally change the Constituent ids to remove the hyphen?

Thanks. For example, my ID might be 09-52, and Excel is changing it to Sep-52.

I tried exporting it to a Text format, then going into Excel, opening the file, saving as CVS and going through that whole hullabaloo, and it still didn't fix all of the columns. What a pain. I do think I want to globally re-assign the constituent IDs....

Jason Black · February 2015

Julie Hiland:
Thanks. For example, my ID might be 09-52, and Excel is changing it to Sep-52.

I tried exporting it to a Text format, then going into Excel, opening the file, saving as CVS and going through that whole hullabaloo, and it still didn't fix all of the columns. What a pain. I do think I want to globally re-assign the constituent IDs....

Excel makes assumptions about what data are contained in your files - it's supposed to be very, um. . . helpful. Unfortunately you are experiencing one of it's common side effects. There are solutions, but they all hinge on what you've already mentioned - export to text, import in Excel and set any affected columns to import as text. This works well, but does add steps every time you export a file. If you export a lot of similarly formatted lists, you can record simple macros in excel to import them properly.

But I think your idea of re-assigning the constituent IDs via import may be your best solution. If you like your current format, you can use Excel's find and replace to change the hyphen "-" into something Excel won't translate into a date separator, such as an underscore"_" (after opening your import file properly in Excel, that is).

JoAnn Strommen · February 2015

Jason Black:
Excel makes assumptions about what data are contained in your files - it's supposed to be very, um. . . helpful. Unfortunately you are experiencing one of it's common side effects. There are solutions, but they all hinge on what you've already mentioned - export to text, import in Excel and set any affected columns to import as text. This works well, but does add steps every time you export a file. If you export a lot of similarly formatted lists, you can record simple macros in excel to import them properly.

But I think your idea of re-assigning the constituent IDs via import may be your best solution. If you like your current format, you can use Excel's find and replace to change the hyphen "-" into something Excel won't translate into a date separator, such as an underscore"_" (after opening your import file properly in Excel, that is).

As Jason said it's more of an issue with excel formatting that the data pulling from RE. If you haven't, you could check the excel help info. I remember seeing this issue several years ago.

Meanwhile, changing ID format is a good option. We use year in ID and I haven't noticed a problem when I've exported. Maybe you could use the Import ID instead of the constituent ID - just a thought.

Julie Hiland · February 2015

JoAnn Strommen:
As Jason said it's more of an issue with excel formatting that the data pulling from RE. If you haven't, you could check the excel help info. I remember seeing this issue several years ago.

Meanwhile, changing ID format is a good option. We use year in ID and I haven't noticed a problem when I've exported. Maybe you could use the Import ID instead of the constituent ID - just a thought.

Curious what formats other orgs use. JoAnn, you said you use year. How so? 2015####? Do you start the #### sequence over again each year or keep it going?

JoAnn Strommen · February 2015

Julie Hiland:
Curious what formats other orgs use. JoAnn, you said you use year. How so? 2015####? Do you start the #### sequence over again each year or keep it going?

Our format is one of the RE default, example 2015-346. "Four digit year" followed by "-" and unique "#" in sequence. Auto resets at beginning of fiscal/calendar year. It's set up under Configuration > Business Rules. (Not all users have access to it - need appropriate user rights.)

Mike Bidmead · July 2015

We had this problem so we changed the divider from a dash (-) to a backslash (\\), which Excel will not try to format to a date.

Constituent IDs converting to date in Excel...HELP

Comments

Categories