Forum Discussion
Excel date formatting issue automatically changing date value to month
- Aug 07, 2021
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.
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.