# Formula to concatenate date range based on several factors

1. ## 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.

2. ## Re: Formula to concatenate date range based on several factors

Welcome to the Forum bre_says !

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.
Is there a minimum time from the reappointment and the next scheduled reappointment?

In the example you highlighted in yellow for the result you want, why is the year 2019 and not 2020?

02/01/2019 – 10/31/2019

This can certainly be done in a single formula as you request but it makes it much messier since you have to do the formatting of the dates within the formula. If you are worried about people messing your data up you could use sheet protection.

3. ## Re: Formula to concatenate date range based on several factors

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!

4. ## Re: Formula to concatenate date range based on several factors

Here is the formula I get:

=TEXT(B2+1,"mm/dd/yyyy") & " – " & TEXT(IF(EOMONTH(DATE(YEAR(B2)+2,MONTH(A2),1),0)<=EDATE(B2,24),EOMONTH(DATE(YEAR(B2)+2,MONTH(A2),1),0),EOMONTH(DATE(YEAR(B2)+1,MONTH(A2),1),0)),"mm/dd/yyyy")

I implemented it in your file. Also I put in a check against your sample data, and I think you have two errors where your end dates are past the two-year mark. I used conditional formatting to check them against my results.

I also showed you how I would do this if it were me, but the other way works just as well.

5. ## Re: Formula to concatenate date range based on several factors

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1