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.
Bookmarks