Formula to concatenate date range based on several factors

I am completely new at posting here. Sorry if I'm doing anything wrong. I've tried and tried to find a solution without posting, but it either isn't posted or I don't know what to call what I'm looking for. I am completely self taught at excel and while I am better than the average user, I still don't know a TON and sometimes don't fully understand what I'm using.

I have a spreadsheet that includes a provider's birth date and their scheduled reappointment date. I am trying to find a formula that only uses one column (so no extra columns to calculate dates to concatenate in another column) that will concatenate their scheduled reappointment date + one day and their next reappointment due date. Their reappointment due date has to be within 2 years of their scheduled reappointment and it should be the last day of the month they were born. I've managed to get by using this formula: =CONCATENATE(TEXT(G2+1,"MM/DD/YYYY")," – ",TEXT(EOMONTH(DATE(YEAR(G2)+2,MONTH(G2),DAY(G2)),0),"MM/DD/YYYY")) (I realized today that I could mobe the EOMONTH), but sometimes the providers reappoint out of cycle for various reasons (e.g., something happened and they reappoint 06/30/18 but their birthday is in 12/15/60). When that happens, I need the date range to calculate from 07/01/18 to the next 12/31 that is within 2 years of 07/01/2018.

I tried this formula: =CONCATENATE(TEXT(B2+1,"MM/DD/YYYY")," – ",TEXT(DATE(YEAR(B2)+2,MONTH(A2),DAY(EOMONTH(A2,0))),"MM/DD/YYYY")), and it gives me the correct day based on their birthdate but it will push out past 2 years.

I tried this formula: =CONCATENATE(TEXT(B2+1,"MM/DD/YYYY")," – ",TEXT(IF(DATE(YEAR(B2)+2,MONTH(A2),DAY(EOMONTH(A2,0)))>B2+1, DATE(YEAR(B2)+2,MONTH(A2),DAY(EOMONTH(A2,0))),DATE(YEAR(B2)+1,MONTH(A2),DAY(EOMONTH(A2,0)))),"MM/DD/YYYY")) with both less than and greater than in the IF function, and it's still not doing what I need it to do.

It's really important for me to be able to get this formula to only use one column because I have people using this spreadsheet who have no idea what they are doing and they are adding and removing information which has messed up the information several times. I've attached a sample SS with the birthdate, the scheduled reappointment date, both of the formulas I've tried above, and what I need the dates to look like, with the important date highlighted.

There isn't really a minimum time. I just need it pushed as far out as possible while staying within two years and being on the last day of the month of their birth.

OOPS! That should have been 10/31/2020; so E17 should be 10/31/2020. The issue with the two formulas is that they are pushing to 2021 which is too far.

I tried protecting the workbook, but I kept running into issues because there are so many fields we do have to edit. And it was randomly un-protecting cells somehow. I also needed the columns hidden because we copy and paste straight into our board report, which is a table in a word document. My fellow teammates maintain some of their own data and don't understand hiding, un-hiding, and dragging.

For a little more background, we use a very limited online credentialing database that houses all of our information. We pull reports out of that database and use SSs for tracking. To prepare for our monthly meetings, we have to add every single item (usually close to 100) onto a Word document to be presented to committees and the board. Before I started using my SS, they were manually typing everything onto the word document. With the amount of info we add, it was time consuming and silly. I've attached an example of my entire SS if that helps.

I don't mind if it is messy if it works. But if there isn't a good way to do it, I will find another solution.

Thanks!

Awesome, thank you!! Both of your examples work for me, and they are much easier for me to follow along to figure out what I'm doing. Thanks again!