+ Reply to Thread
Results 1 to 5 of 5

Formula to concatenate date range based on several factors

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    Springfield, IL
    MS-Off Ver
    Excel 2010
    Posts
    3

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,679

    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.
    Jeff
    | | || || || || | |:| | || ||
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    08-22-2013
    Location
    Springfield, IL
    MS-Off Ver
    Excel 2010
    Posts
    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!
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,679

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-22-2013
    Location
    Springfield, IL
    MS-Off Ver
    Excel 2010
    Posts
    3

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula that pulls across a value based on the which date range the given date falls into.
    By h2holbro22 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2018, 04:30 PM
  2. [SOLVED] IF Formula using multiple date factors
    By kschmit1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-08-2017, 09:30 AM
  3. Replies: 1
    Last Post: 11-24-2015, 03:00 AM
  4. [SOLVED] Formula to calculate total of a series of data based on other factors
    By neurion in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2015, 08:44 AM
  5. formula to calcualte number based on several factors
    By shlomo821 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2013, 08:23 PM
  6. Replies: 6
    Last Post: 07-31-2009, 10:58 AM
  7. Calculating a range of values based on 2 factors
    By chrish8 in forum Excel General
    Replies: 6
    Last Post: 02-02-2009, 11:32 PM

Bookmarks

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