MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Change the Dates In Column?

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

Comments: 6 Responses to “How Do I Change the Dates In Column?”

    3 years ago

    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.

    Antonius
    3 years ago

    Thanks Gary this saves me a lot of work!

    Antonius
    3 years ago

    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?

    3 years ago

    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?

    Antonius
    3 years ago

    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.

    Antonius
    3 years ago

    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.

Comments Closed.