Forum Discussion

Tjin's avatar
Tjin
Copper Contributor
Jan 22, 2025

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.

    • Tjin's avatar
      Tjin
      Copper Contributor

      Thank you. I guess I'll just change it to text then.

Resources