Excel date formatting issue automatically changing date value to month

[jishnuchandra1]

I am having one issue such that changing my date field as month field! I have used format cell option and used custom format M/DD/YYYY and entered 01/08/2018. but that is changing to 08/01/2018!

Please find the screenshot attached. Could you please help me to resolve the issue. Because of which I'm not able to create forecast.

[#2 · Accepted solution]

@jishnuchandra1 

You must always enter dates according to your system date format, regardless of how the cell is formatted.

I assume that your system date format is dd-mm-yyyy. If you enter 01/08/2018, Excel interprets it as the 1st of August, 2018, and shows 01-08-2018 in the formula bar.

Formatted as m/dd/yyyy, this is displayed as 8/01/2018 in the cell.

If you want to enter the 8th of January, 2018, you must enter it as 08/01/2018 or 8-1-18 or similar because of the dd-mm-yyyy system date format. You'll see 08-01-2018 in the formula bar.

Formatted as m/dd/yyyy, you'll see 1/08/2018 in the cell.

[#3]

@jishnuchandra1 

=TEXT(A1,"M.DD.YYYY")

=TEXT(Value you want to format, "Format code you want to apply")

TEXT function

Note: Although you can use the TEXT function to change formatting, it’s not the only way. You can change the format without a formula by pressing CTRL+1,

then pick the format you want from the Format Cells > Number dialog.

Various examples can be found in the inserted file.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

[#4]

@jishnuchandra1 

In addition, please note that dates in Excel are actually sequential starting from 01 Jan 1900 (if you are on Windows). In particular, Aug 01, 2021 is actually number 44409. Applied format is only defines how to show this number, not how to enter it.

How to enter is actually defined in Windows settings, do you have default date format as dd/mm/yyyy or m/d/yy or like.

[#5]

Thank you Hans. This was the issue and I am able to successfully complete my work! Appreciate your help here :)

[#6]

Thanks to @NikolinoDE and @Sergei Baklan for the suggestions !

[#7]

@jishnuchandra1 , glad to help