Dates do not show in the correct format in csv

[LindseyM]

We're using 4.3.7 and are storing dates. I have put them in as dd/mm/yyyy and in some places as dd-mm-yyyy but when we export to csv it shows the date in the format October 1st, 2013. It's the same in the 4.3 demo.

This is hard to convert into a date format without some fancy formulas and means us doing another step each time we export. If it formatted it dd/mm/yyyy or anything vaguely similar, even as a text field rather than date field, it'd be much more straightforward.

How can we get the date to show in csv as it's formatted in the custom field?

[Dave Greenberg]

Lindsey - I think you might be seeing the date as 'displayed' by Excel based on Excel's configured rules for date display (if you're viewing the export in Excel). I just did "Export Primary Fields" from a simple contact search on the 4.3 demo. When I view it in Excel, the birth dates look like this:

5/1/63

... but when I look at the .csv file in a text edit, the actual date format is this (which I think is our standard storage format for dates in the database):

"1963-05-01"

[LindseyM]

Thanks for the speedy reply, Dave. I'll pass this on to the colleague who was stumped by it!

[LindseyM]

My colleague tried this and said:

If I repeat what Dave did, yes, the birth dates come out as a date format on the csv/Excel versionI download. However, that is not true for the other dated custom fields we created. They come out as text.  It is not an issue with Excel displaying dates - the fundamental format of the data is as text and not as a date.

I need to check if our dated custom fields are set up in a different way to the Birth date one, but I don't know where to find the Birth date set up as it's not a custom field. Please can you point me to it. I've attached a screenshot of one of the custom fields where the date doesn't display properly in the export in case you can see the issue from that.

[kennedy]

check your CiviCRM date format on this link: civicrm/admin/setting/date?reset=1
you can also go to  "Advanced Date Input Settings" from this same link and see if the date format matches that of your custom field.

Hope this helps.

[jcasharpe]

I've just been looking into this for LindseyM and the problem is that in CRM/Export/BAO/Export.php custom fields are rendered by calling CRM_Core_BAO_CustomField::getDisplayValue, which in turn calls CRM_Utils_Date::customFormat but with only one parameter so the date format is not configurable for a Custom Field with type date.

[Dave Greenberg]

Have you come up with a patch to make it configurable / respect the existing configuration for 'custom' date class? If so, this could potentially be added to core for 4.5 (PR against master on GitHub would be best).

[jkirsner]

Any update on this?

[Dave Greenberg]

Haven't heard anything :-(

[jcasharpe]

I put in a fixed string into Export.php that matches what we need. IIRC there are a number of custom date formats configurable and it wasn't clear which made sense for the custom fields; ideally each custom field would have its own custom date format.

[Jon-man]

We are experiencing the same issue. For custom data date fields, even though the format is configured  one way (e.g. dd/mm/yyyy), the csv export for the date field is coming out as [month name] [day], [year] (e.g. January 1st, 2014).

I think I agree with jcasharpe, each custom date field should have its own export format.