# Date format inconsistencies

**URL:** <https://forum.manager.io/t/date-format-inconsistencies/31344>\
**Category:** Uncategorized\
**Created:** [October 26, 2020, 5:09pm UTC](https://forum.manager.io/t/date-format-inconsistencies/31344 "2020-10-26T17:09:30Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ealfardan](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/ealfardan/32/63166_2.png) [@Ealfardan](https://forum.manager.io/u/Ealfardan)\
**Post date:** [October 26, 2020, 5:09pm UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/1 "2020-10-26T17:09:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Tut](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/tut/32/3286_2.png) [@Tut](https://forum.manager.io/u/Tut)\
**Post date:** [October 26, 2020, 6:04pm UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/2 "2020-10-26T18:04:02Z")

</div>

I moved the topic to bugs.

---

<div class="post-metadata">

**Author:** ![lubos](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/lubos/32/62798_2.png) [@lubos](https://forum.manager.io/u/lubos)\
**Post date:** [November 4, 2020, 1:12am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/3 "2020-11-04T01:12:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Tut](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/tut/32/3286_2.png) [@Tut](https://forum.manager.io/u/Tut)\
**Post date:** [January 5, 2021, 7:12pm UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/4 "2021-01-05T19:12:27Z")

</div>

Split this topic on January 5, 2021, 7:12pm UTC

A post was merged into an existing topic: [Problem with batch update](https://forum.manager.io/t/problem-with-batch-update/32415/4)

---

<div class="post-metadata">

**Author:** ![eko](https://avatars.discourse-cdn.com/v4/letter/e/a8b319/32.png) [@eko](https://forum.manager.io/u/eko)\
**Post date:** [April 26, 2021, 9:56am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/5 "2021-04-26T09:56:22Z")

</div>

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).

 ![Screenshot 2021-04-26 at 10.54.36](https://us1.discourse-cdn.com/flex019/uploads/manager1/original/3X/7/b/7bf923804697ee9e91a6d8baf81a05b722afe047.jpeg)

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

---

<div class="post-metadata">

**Author:** ![Ealfardan](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/ealfardan/32/63166_2.png) [@Ealfardan](https://forum.manager.io/u/Ealfardan)\
**Post date:** [April 26, 2021, 10:06am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/6 "2021-04-26T10:06:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Patch](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Patch](https://forum.manager.io/u/Patch)\
**Post date:** [April 26, 2021, 10:16am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/7 "2021-04-26T10:16:58Z")

</div>

- make a backup

- Agree with choosing a non ambiguous format at least while correcting the problem.

- use batch update to translate dates

---

<div class="post-metadata">

**Author:** ![eko](https://avatars.discourse-cdn.com/v4/letter/e/a8b319/32.png) [@eko](https://forum.manager.io/u/eko)\
**Post date:** [April 26, 2021, 10:22am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/8 "2021-04-26T10:22:34Z")

</div>

> [@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!

---

<div class="post-metadata">

**Author:** ![lubos](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/lubos/32/62798_2.png) [@lubos](https://forum.manager.io/u/lubos)\
**Post date:** [April 26, 2021, 10:35am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/9 "2021-04-26T10:35:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![eko](https://avatars.discourse-cdn.com/v4/letter/e/a8b319/32.png) [@eko](https://forum.manager.io/u/eko)\
**Post date:** [April 26, 2021, 10:45am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/10 "2021-04-26T10:45:35Z")

</div>

> [@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 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

[https://www.extendoffice.com/documents/excel/4646-excel-convert-dd-mm-yyyy-to-mm-dd-yyyy.html](https://www.extendoffice.com/documents/excel/4646-excel-convert-dd-mm-yyyy-to-mm-dd-yyyy.html)

Give wrong results see below screenshot

![Screenshot 2021-04-26 at 11.45.17](https://us1.discourse-cdn.com/flex019/uploads/manager1/original/3X/9/9/9902c35265b672131c2d21787007f1ad04dd0446.png)

---

<div class="post-metadata">

**Author:** ![Ealfardan](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/ealfardan/32/63166_2.png) [@Ealfardan](https://forum.manager.io/u/Ealfardan)\
**Post date:** [April 26, 2021, 10:51am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/11 "2021-04-26T10:51:43Z")

</div>

> [@eko](#):
>
> =DATE(VALUE(RIGHT(A9,4)), VALUE(MID(A9,4,2)), VALUE(LEFT(A9,2))) at

It should be this

```auto
=IF(ISNUMBER(A9),DATE(YEAR(A9),DAY(A9),MONTH(A9)),DATE(RIGHT(A9,4),MID(A9,4,2),LEFT(A9,2)))

```

---

<div class="post-metadata">

**Author:** ![Patch](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Patch](https://forum.manager.io/u/Patch)\
**Post date:** [April 26, 2021, 10:55am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/12 "2021-04-26T10:55:36Z")

</div>

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 format

- batch re import the entries requiring correction

---

<div class="post-metadata">

**Author:** ![eko](https://avatars.discourse-cdn.com/v4/letter/e/a8b319/32.png) [@eko](https://forum.manager.io/u/eko)\
**Post date:** [April 26, 2021, 11:02am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/13 "2021-04-26T11:02:30Z")

</div>

> [@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 12.00.39](https://us1.discourse-cdn.com/flex019/uploads/manager1/original/3X/0/5/054d900ed66a06e099fcb903f1958900bc6957e1.png)

---

<div class="post-metadata">

**Author:** ![Ealfardan](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/ealfardan/32/63166_2.png) [@Ealfardan](https://forum.manager.io/u/Ealfardan)\
**Post date:** [April 26, 2021, 11:07am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/14 "2021-04-26T11:07:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Patch](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Patch](https://forum.manager.io/u/Patch)\
**Post date:** [April 26, 2021, 11:15am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/15 "2021-04-26T11:15:52Z")

</div>

> [@Patch](#):
>
> Look at which dates are actually wrong. If the day is greater than 12 the date format is not ambiguous.

Did you do this?

---

<div class="post-metadata">

**Author:** ![eko](https://avatars.discourse-cdn.com/v4/letter/e/a8b319/32.png) [@eko](https://forum.manager.io/u/eko)\
**Post date:** [April 26, 2021, 11:38am UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/16 "2021-04-26T11:38:25Z")

</div>

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. 👍🏻

---

<div class="post-metadata">

**Author:** ![Ealfardan](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.manager.io/ealfardan/32/63166_2.png) [@Ealfardan](https://forum.manager.io/u/Ealfardan)\
**Post date:** [January 18, 2023, 2:46pm UTC](https://forum.manager.io/t/date-format-inconsistencies/31344/17 "2023-01-18T14:46:24Z")

</div>

Closed on January 18, 2023, 2:46pm UTC


