Forum Discussion
Can we prevent Excel from auto-appending year?
Would appreciate it if someone could help me with this little problem:
I have a spreadsheet where I do reconciliation of monthly sales, and I usually key in the dates as dd-yyy. Eg, for a payment received on 15th of January 2025 I'll key in "15-Jan" without the year.
I only found out today that my December sales reconciliations have mixed dates: https://drive.google.com/file/d/1_DMfXAvKRkHJLXf4tjExvHpcoP_nUu_V/view?usp=drive_link
I went back to check my December-2023 sales reconciliation file and it does have mixed 2023/2024 dates as well. Other months don't seem to have this problem (I've only checked a few files).
I'm guessing that:
a) those that I keyed in on or before Dec-2024 will have the year as "2024", and
b) those that I keyed in after 1-Jan-2025 got tagged as "2025" - cos I remember working on rows 8/14/21/54 in the above screenshot in January of this year.
I suppose keying in the full date (day/month/year) will resolve this problem, but if I don't intend to use this data in calculations is there a way to stop Excel from auto-completing the year to the entry itself?
Formatting it to "dd-mmm" looks fine on my end, but when I upload the file somewhere and another user downloads the file, somehow that person ends up seeing the mixed year (which was how I found out about this issue). And that's kinda annoying because the other user flagged it as wrong data entry.
2 Replies
A 'real' date in Excel must have a year. If it's just for display purposes you can either format the cell as text before entering a value such as 15-Jan, or prefix the value with an apostrophe: '15-Jan. The apostrophe forces Excel to treat the value as text. It will be displayed in the formula bar but not in the cell itself.
- TjinCopper Contributor
Thank you. I guess I'll just change it to text then.