donation_date field is not in date format
complete
Deana & Jim Cavan
I am trying to import donations from the donor data download to our constituent database (Little Green Light). When I try to import, it is telling me the date is invalid. I believe it's because it's a text string. I cannot easily convert this to a date that excel recognizes (even with DATEVALUE function). I don't know how anyone works around this. Would love some insight or a date field that is not written out in text.
Clair Lofthouse
complete
We are excited to let you know that your suggestion has been implemented! Thank you again for collaborating directly with us to build better fundraising tech.
Your suggestion was a part of the new report release, "Line Items (All)" now found in the Nonprofit Admin Portal, under "Data Reports" in the left-hand navigation. The other Canny suggestions below were also part of this update if you want to check them out:
- feedback.givelively.org/data-reporting/p/add-net-amount-to-donor-export
- feedback.givelively.org/data-reporting/p/individual-breakdown-of-donations
- feedback.givelively.org/data-reporting/p/create-receiptsentat-column-for-donor-data-exports
- feedback.givelively.org/data-reporting/p/zip-codepostal-code-not-formatted-in-export
- feedback.givelively.org/nonprofit-admin-portal/p/pull-donor-data-without-exporting-csv
- feedback.givelively.org/nonprofit-admin-portal/p/reformat-donation-date-field-in-exported-data-not-currently-readable-as-date-by
Kate Hibbert
This is bothersome to me too. In addition to the Text to Column tool mentioned below, I have used Find and Replace in Excel as a workaround: Find "Nov " and replace with "11/" and then Find " 2021" and replace with "/2021" etc. Make sure to include the spaces. Hope that's helpful to you! But I really hope GiveLively updates the way the date field is formatted to something readable by excel.
L
Lisa Sandoval
Although we don't have this issue because we use the live Salesforce integration, agreed that this is annoying.
Here's how you can fix this yourself in Excel, using Text to Columns. This method creates an end result with the date in one column and the time in another - I'm assuming you don't really need the time.
- Add an additional column to the right of the donation_date column
- Select the donation_date column
- Under Data, choose Text to Columns
- Select Fixed Width, then click Next
- Double click the excess divider lines to leave only one at the end of the date, then click Next
- Set the "column data format" to Date, then click Finish