Hi, Does anybody know how to make a column with dates from 2021 change into dates from 2020.
Thanks for sharing!
I would like to do the financial reports from last year and I filled in all the dates but it automatically makes it 2021. I can change it by hand but I think there must be some kind of trick to do it.
—–
Antonius
Say your bad dates are in column B. Insert a new column after it, C. Then in C2, place this formula:
DATE(2020,MONTH(B2),DAY(B2))
What this does is create a new date in that cell with the year 2020. The month is taken from the date in B2, and the date is taken from the date in B2. So if B2 was 5/3/2021, then C2 will show 5/3/2020.
Copy and paste that throughout column C so C now shows the 2020 version of all of the dates in column B. Then select all of those cells in C and Edit, Copy. Then, with those cells still selected, use Edit, Paste Formula Results.
Now you have those dates in column C like you want. So just delete column B and you are set.
Thanks Gary this saves me a lot of work!
Hi Gary,
It keeps saying I have a syntax error. I have DATE(2020,MONTH(A2),DAY(A2)) exactly as you said.
What can be wrong?
Antonius: I copied and pasted what you posted in your comment into B2 and had a date in A2 and it worked. Are you perhaps in a country where a semicolon is used rather than a comma?
Ok I found it, it had some problems with the brackets. I have 2 kind of brackets, this ( and a pre defined one from numbers.
Yes that is right, this one ;
I live in Portugal, come from the Netherlands and bought the computer in London. So it could be confused.