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 anywa…

Please check the latest version (20.10.59). Should be resolved now.

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 pro…

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.

make a backup Agree with choosing a non ambiguous format at least while correcting the problem. use batch update to translate dates

[image] Ealfardan: ISO time format yyyy-mm-dd or any other variation with the year first. Did not know that ISO time formats were consistent like this, thank you learned something new today!

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.

[image] Patch: Agree with choosing a non ambiguous format at least while correcting the problem. 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 exce…

[image] eko: =DATE(VALUE(RIGHT(A9,4)), VALUE(MID(A9,4,2)), VALUE(LEFT(A9,2))) at It should be this =IF(ISNUMBER(A9),DATE(YEAR(A9),DAY(A9),MONTH(A9)),DATE(RIGHT(A9,4),MID(A9,4,2),LEFT(A9,2)))

I would use more working columns split the date into a separate column for day, month and year Look at which dates are actually wrong. If the day is greater than 12 the date format is not ambiguous. select the erroneous dates construct a formula to reassemble the date in the correct form…

[image] Ealfardan: =IF(ISNUMBER(A9),DATE(YEAR(A9),DAY(A9),MONTH(A9)),DATE(RIGHT(A9,4),MID(A9,4,2),LEFT(A9,2))) 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): [Screenshot 2021-04-26 at 1…

Date format inconsistencies

Topic		Replies	Views
Messed up dates possible bug or someting else	26	865	September 4, 2021
Batch Create and Update Date fields Bug	20	1626	April 4, 2021
Data changed overnight	25	1017	October 7, 2020
Date Custom Fields in Custom Reports and Batch Operations ideas	13	1374	March 17, 2021
Batch Update program fault / limitations	7	897	June 2, 2021

Date format inconsistencies

Related topics