hi
i am trying to create a column that's that reads another column of varying ages of people and project their next medical date. The projected dates need to be <40 add 4 years >40 add 2 years and >50 add 1 year.
thanks
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Date Projection
0
Answers
0
Selected Answer
Here is the formula you have been looking for. Paste it in a cell in row 2 and copy down as required.
=WORKDAY(DATE(YEAR($B2)+INDEX({1,2,4},MATCH(DATEDIF($A2,$B2,"Y"),{100,50,40},-1)),MONTH($B2),DAY($B2))-1,1)The formula requires the person's birthday in column A and the previous check-up date in column B. And this is how it works. Remember to resolve Excel formulas from the center to the left.
DATEDIF($A2,$B2,"Y") calculates the number of years between birthday and check-up, meaning the next check-up wil be determined by the person's age at the time of his last check-up. When he turns up after 4 years he will be older.
MATCH(DATEDIF($A2,$B2,"Y"),{100,50,40},-1)) looks for a match of the age in the array {100,50,40}. Meaning you can't set a checkup for persons of more than 100 years of age (unless you change the formula here). It also means that a person 50 years of age isn't "more than" 50 years old. If you want to see that person sooner, reduce the number to 49.
The MATCH function will return the number 1, 2 or 3. This number is used to select one of the numbers in INDEX({1,2,4},MATCH. It will be either the first, second or third, meaning 1 year, 2 years or 4 years.
The DATE function's syntax is very simple. DATE(Year, Month Day) returns a date. So, the formula takes the date of the last check-up and adds the numbers of years first calculated to the Year component of the DATE function.
Of course, this date might well be a Sunday. Therefore the entire date calculation explained above is embedded in a WORKDAY function. This function is tricked into providing a workday. The formula takes the calculated day, minus one day (=the day before that date) and asks which would be the next workday if we add the taken day back: WORKDAY([Date]-1,1).
Discussion
Hi there thank you very much for doing that and it does work to a point. It only gives me a return date of 4 years for everyone. I am trying to get a return date to show everyone under 40 4 yrs from medical date, over 40 +2 yrs from medical date and all over 50 1 yr. I would attach an example but cant see how to do that sorry. If i do see a way then i will post it
Bosh (rep: 6) Feb 2, '18 at 3:24 am
The formula does what you want. Check your column references. Age is calculated from the difference between last checkup date and birthday. The result you have seems to indicate that the age isn't calculated correctly which is likely to be an error in the data of your test. You can test =DATEDIF($A2,$B2,"Y") by itself to check your data. You can replace DATEDIF($A2,$B2,"Y") in the formula with a hard age (like 40 or 50) to check the formula's functionality.
Variatus (rep: 4889) Feb 3, '18 at 8:32 pm