Share via

Excel Bug

Anonymous
2022-10-02T10:36:51+00:00

Im having trouble with the dates.

se every time i write a date, it works perfectly good(dd/mm/yyyy)

but for some reason, when i download or send a file, it flips the date. not in the format but actually treats it like i wrote 09/01/2022 instead of 01/09/2222.

thank u in advance :)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-10-02T14:29:01+00:00

    I suspect that you are talking about a CSV file, not an Excel file (xls, xlsx, xlsm or xlsb).

    In an Excel file, dates are represented by an integer. For example, enter the date 9/1/2022 into A1, then enter formula =A1 into A2 and format A2 as General. You will see 44570 if the system date form is DMY. You will see 44805 if the system date form is MDY.

    Suppose you see 44570 in A2. You will continue to see 44570 in A2 even if you reformat A1 to display m/d/yyyy.

    When the Excel file is saved as CSV, the characters 9/1/2022 are written to the file, not the integer.

    If you open the CSV file on a computer where the system date form is MDY, A2 will display 44805.

    If you open the CSV file on a computer where the system date form is DMY, A2 will display 44570.

    Practically speaking, there is nothing you can do about that.

    You need to format dates as they will be interpreted on the receiving system before you save as CSV.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-10-02T14:12:31+00:00

    Hello Eylon,

    So sorry you are encountering this issue.

    Excel's interpretation of dates depends on the date formats specified in the Regional and Languages control panel.

    Yu entered your date in the UK style, but excel displays it in US style after you download or send.

    Kindly confirm that your region and language is "English(UK)"

    -Open Excel > Click File > Options > Regional Format Settings.

    -Click the regions drop-down, select a region, and then click Change

    Secondly, regional format can also be set from the Number Format dialog

    -Open Excel >Click Home > Number Format > More number formats...

    -Under Category, click Date, Time, or Special, and then select a locale from the Locale menu

    -Select the checkbox to set the chosen locale as your default regional format, and then click OK.

    I hope this helps.

    Regards, Yusuf

    Was this answer helpful?

    0 comments