The way in which are displayed in Donorfy or appear after being uploaded or downloaded can occasionally take a different format from what you were expecting.
For example, you may have uploaded a file containing dates that appeared to be dd/mm/yyyy but when checked in Donorfy appear as mm/dd/yyyy
Or, it may be that they are dd/mm/yyyy in Donorfy, but download as mm/dd/yyyy
There are a number of possible causes for this, we have listed the most common ones below:
Date format settings
You can set how Donorfy displays dates. It will be defaulted to en-GB and United Kingdom, but may have subsequently been changed.
This setting is available to view by an Administrator role and can be found at Settings > Account > Donorfy Settings
Occasionally when you upload a file to Donorfy the checking process will report errors with dates in the file even though the dates look correct when you open the file in Excel.
This problem is caused by the way Excel sometimes stores dates, to fix the problem you need to do the following:
- Open the file in Excel
- Insert a new column next to the date column which has the errors in it - in the example below the original column is T, the new column is U
Add a formula into the new column for each row with data - the formula is =DATEVALUE( NN ) replace NN with the address of the cell containing the date - e.g. = DATEVALUE(T2)
- The dates in the new column should look the same as the old column
- Next highlight all the data in the new column, copy it, then use paste special to paste in values into the original column
- Check the dates look correct
- You can now delete the new column (U)
- Save your file
- Upload the file into Donorfy
If you get a ~VALUE! error, then please see this Microsoft article: How to correct a #VALUE! error
Strip out hidden formatting
Data being copied into the spreadsheet may be bringing in hidden formatting, which on upload causes dates to appear incorrect. Instead of using the standard way of copy and paste, use Paste Values. This strips out all of the unrequired and hidden formatting that may have been gleaned from the data source.
When Pasted as Values – set your date in Excel by highlighting the columns and selecting Date or Short Date.
Website Browser settings
Your website browser settings may need setting to the correct language and date format. See this article for how to correct: Dates are showing in US format (MM/dd/yyyy)
- Field formatting – have you selected the correct date format for the column /cells that feature dates?
See Office.com Support article
- Your Excel settings may need configuring to display the correct language and date format, for example from English (US) to English (United Kingdom)
To do this see this Office.com Support article
- Your MS Office global settings may be incorrect for Office365 – see this Office.com Support article
- Your Network global settings may be overriding your local machine settings – your IT professional will need to alter the group policy for Language and Date settings
Sometimes, no matter what changes you do the issue still occurs. In these cases, it may be that uninstalling and re-installing Excel is the only way to resolve the matter. Any cached or corrupt settings would be wiped by this meaning you can set up Excel correctly. By default, MS products use English (US) so be mindful to change this to the preferred set up rather than just opting for the defaults.
It's a shame the checking process can't pick up wrongly formatted dates - just had to re-do 55000 rows