Forum Discussion

Copper Contributor

May 21, 2020

Find and Replace Partial numbers

I realize this is a simple problem, but after spending a ridiculous amount of time reading and researching, I can't figure it out! I have a very long list of birthdates that, when uploaded, were ch...

Excel on Mac

mtarler

Silver Contributor

May 22, 2020

arise2read your approach seems limited unless you are sure none of the birthdays are in the 2000's. I would instead suggest making a new column with a formula to convert it. For example maybe you are sure none are <5 years old (i.e. no birthdays after 2015):

=if(year(a1)>2015, date(2019,month(a1),day(a1)),a1)

that will get you an Excel DATE in whatever format your excel is set to display or if you specifically want the output as text

=if(year(a1)>2015, "2019" & month(a1) & day(a1),a1)

If you don't want that extra column, then once you are done you can copy the column of correct data and 'paste special' -> 'values only' and then delete the formula and incorrect data columns.

arise2read

Copper Contributor

May 22, 2020

mtarler This is excellent, as none of our volunteers will be under the age 16, but there are a few that will have been born after 2000.

However, I am embarrassingly CLUELESS when it comes to formulas. Can you give me specific directions on how to complete this? Should I copy and paste the existing birthdate column to a new column, and put the formula =if(year(a1)>2015, date(2019,month(a1),day(a1)),a1) in the row above that? Do I copy this formula exactly?

Thank you for your help!