Forum Discussion
Find and Replace Partial numbers
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!
arise2read the formula was 'generic' assuming the dates of interest are in column A and starting in row 1 and that you would past the formula and fill down accordingly.
Quick lesson in formulas:
=if(year(a1)>2015, date(2019,month(a1),day(a1)),a1)
the "a1" listed refers to cell (A1).
so if you have all the birthdays in column A starting in A1 you could paste this in B1 and then as you copy or fill down, excel with change the "A1" in the formula to A2, A3, A4, ... so that the cell it is pointing at is always in the same relative location (i.e. the cell to its left)
If you don't want excel to automatically change that pointer then add a "$" in front of what you don't want to change.
for example if instead of "2015" you want to use what ever year you type into cell F1 then use $F$1 so as you fill down excel won't change it and all the cell formulas down the column will still point to $F$1 (note: because you are only filling down in 1 column the relative column isn't changing so you could use F$1 since the F wouldn't change any how)
that said, if your birthdays start in D2 (i.e. D1 is a title) then change all the "a1"s to "D2"s and paste it into cell E2 and fill down.
These are some very important basics you should play with and learn and maybe watch a youtube on if needed because they will help you a lot.