Forum Discussion
Excel date formatting issue automatically changing date value to month
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.
=TEXT(A1,"M.DD.YYYY")
=TEXT(Value you want to format, "Format code you want to apply")
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.