Forum Discussion
Solved
Excel - format cells date
Hi. I have resolved how to make a whole spreadsheet default to a year that is not the current year (ie, working in 2022, but wanting all dates to be recorded as 2023) by using the Custom Format: ddd dd mmm "2023"
However, when I type 20 Oct into the cell, it correctly defaults to the year as 2023 (as per the format request), but the Day of the week is incorrect and is still showing as the 2022 day. eg, I type in 20 Oct, and the cell returns Thu 20 Oct 2023. This is incorrect as 20 Oct 2023 is actually a Friday.
Does anyone know how I can resolve this?
Many thanks
SueB33 As you have discovered, formatting a date by adding a piece of text to it, doesn't change the underlying date. Rather than just typing 20 Oct, type 20oct23.
That's one extra keystroke and excel will recognize it as a date in 2023.
And set the format to ddd dd mmm yyyy
- Alternative way, copy number 364 and select the range after entering current year date data then use paste special under operation click add and click OK. Exact one year will be added for entire column.
SueB33 As you have discovered, formatting a date by adding a piece of text to it, doesn't change the underlying date. Rather than just typing 20 Oct, type 20oct23.
That's one extra keystroke and excel will recognize it as a date in 2023.
And set the format to ddd dd mmm yyyy
Thank you so much Riny_van_Eekelen
I was hoping not to have to type in the year, but as you point out, it is only a couple of extra keystrokes 🙂
Regards
Sue
