I have been facing lots of problems in import/export and batch operations lately due to the inconsistencies in the date formats as of date.
In our country (as well as almost all other countries) we use dd/mm/yyyy and that what I selected in my preferences but Manager seems to force mm/dd/yyyy anyway for batch operations which causes batch updates to mess up the entire data unless we manually manipulate the dates every time we do a batch update, which is a huge downgrade from like 3 months ago, where everyone can use the format they select in the preferences screen.
Also, the import bank statements does not accept any thing other than mm/dd/yyyy which does not make sense for 90% of people (including banks, my staff and my clients) who do not use this format.
I tried changing my preferred format to ISO to solve this problem and it only worked during batch create, while batch updates and bank statement imports still suffer from the inconsistencies.
I have seen a couple posts before discussing this issue but there seems to be no interest in addressing it.
@lubos, @Tut, @Brucanna, @Abeiku, please categorize this as a Bug so it gets solved because the wasted efforts is becoming a huge PITA, especially since we didn’t have to deal with this like 3 months ago.
We just discovered that for 2017, 2018, and 2019 the dates are mm/dd/yyyy while they should have been dd/mm/yyyy When the new Date & Number Format in Settings was established and we corrected the date. I just checked these settings, see screenshot below and noticed that it is not clear from the prompt if it is 5 April 2021 or 4 May 2021 (by the way server system date is Mon Apr 26 11:53:32 CEST 2021).
Obviously our accounts are a mess now and we need to figure out how to correct these and which ones for thousands of entries. Any advice on how to best approach the latter appreciated but also how to prevent this happening. Thanks
If go to the end, you can see the ISO time format yyyy-mm-dd or any other variation with the year first.
These formats all have similar order so you can tell which is the month or date (in this case it’s 5th of April) and then you can choose any format you like.
I’ve changed the example date to December 31st. The reason for April 5th was to differentiate date formats with/without leading zeroes. But I’m not sure we need so many date formats anyway. So the latest version has December 31st and the number of date formats to choose from has been reduced.
Thank you. I have been “experimenting” with a backup. Using original bank statements as reference to identify which months and years are the problem. I then copied the batch update stuff into excell, Created a new column and would like to apply a formula that would change the entry but all those found such as
=DATE(VALUE(RIGHT(A9,4)), VALUE(MID(A9,4,2)), VALUE(LEFT(A9,2))) at
Thank you but it is weird that this formula only applies to some correctly and makes mistakes in others see screenshot below (all are same date format):
Then, most probably the dates you’re looking into are already correct and the correction just inverted the day and month. Because if excel has read the date wrong, there’s no way excel would assign 27 as the day in 27/02/2018.
You can verify that by sampling one or two of the white lines and one or two of yellow lines.
Good point, I did not do this and indeed these would not be affected and would be right! Thanks, makes “sorting” the faulty ones easier as well. All help offered here much appreciated.
