How to fix date formatting issues in an NDIS Bulk Payment Request file
This error is caused when you open the NDIA Portal Batch CSV file using Excel, Excel tries to be smart and reformats all of the dates in into a date format that Excel prefers (but which the NDIA portal doesn't like). If you need to open the export file in Excel, here's how to do it:
- Open a new Excel sheet, select the Data tab, then click 'From Text' in the Get External Data group.
- Browse to the CSV file and select 'Import'.
- In step 1 of the Import Wizard choose 'Delimited' as the original data type. Click 'Next'.
- In step 2 of the Import Wizard choose Comma as the delimiter (deselect the Tab check box) and click 'Next'.
- In step 3 of the Import Wizard, you tell Excel not to change your formats. With the first column in the Data Preview selected, scroll across to the last column and select it while holding the SHIFT key (all columns should now be selected). Then select 'Text' as the Column Data Format and click 'Finish'.
- Click OK to insert the data into cell A1
You should now have a spreadsheet with the imported data but without Excel breaking the formatting for the NDIA portal. Remember to export it to a CSV again before uploading to the portal if you have made changes.