Cell format

[silky2023]

When exporting a report from a CRM into Excel I'm finding the cell formatting completely random. In my case the lease end date which is always formatted dd/mm/yyyy in the CRM but randomly appears as mm/dd/yyyy in Excel. The only pattern I can see is for some reason the cells in this column seem to have a random format ie if the format is 'general' then the date displays correctly but if the cell is 'custom' then the date changes to the US style mm/dd/yyyy.

Any pointers appreciated

[#2]

@silky2023 

When data is exported from one system to another, especially when going from a CRM system to Excel, formatting inconsistencies can occur due to differences in how each system interprets and handles data formats. It sounds like you are encountering issues with date formatting when exporting data from your CRM into Excel.

Here are a few steps you can take to address the formatting issues:

1. Format Cells in Excel: After importing the data into Excel, you can manually adjust the formatting of the date column. Select the entire date column, right-click, choose "Format Cells," and then select the desired date format (e.g., dd/mm/yyyy). This should override any custom formatting that might have been carried over from the CRM.

2. Text-to-Columns: If the issue persists, you can try using Excel's "Text-to-Columns" feature. This tool can help Excel recognize and correctly format the imported data. Here's how:

1. Select the entire date column.
2. Go to the "Data" tab in the Excel ribbon.
3. Click on "Text-to-Columns."
4. Choose "Delimited" and click "Next."
5. If there is no delimiter to split the dates, select "Next" again.
6. In the "Column data format" section, choose "Date" and select the correct date format (dd/mm/yyyy).
7. Click "Finish."

3. Import Text as Data: If the dates are getting interpreted incorrectly as text, you can force Excel to treat them as dates by using the "Text Import Wizard":

1. Go to the "Data" tab.
2. Click on "Get Data" or "From Text/CSV," depending on your Excel version.
3. Follow the wizard to import your CSV/Text file.
4. During the import process, choose the date column and specify the correct date format.

4. Clear Formatting and Re-Enter: If the data is consistently in the dd/mm/yyyy format in your CRM but is being misinterpreted in Excel, you can try clearing all formatting from the column and then re-entering the data.

5. Check CRM Export Options: If your CRM has any export options or settings related to date formatting, it is worth exploring them to ensure that the dates are being exported in the desired format.

Remember that Excel might interpret dates based on the computer's regional settings, so if you are collaborating with others who might have different regional settings, that can also affect how dates are displayed.

Ultimately, the goal is to ensure that the date column is consistently interpreted and displayed in the desired format in Excel. If the issue persists, you might want to reach out to your CRM's support team to see if they can provide guidance on how to export data with consistent date formatting.

The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.