Continuous Date

[mclovings]

I would like to use a formula that could use a specific linking factor such as a SSN/Name and would be able to dictate whether or not the dates are continuous without a break and give me a consolidated date. Any assistance is greatly appreciated.

[#2]

@mclovings 

You can do date math quite easily. (By the way, the term "continuous date" isn't one I recognize...you use it as if it's common speech).

From what you're showing, if it's the '16 days" you want to have calculated, you could simply enter the formula =D1-C1 (assuming columns begin in your layout with A)

If, on the other hand, you want to know what date is 16 days after 12/15/2020 (and that latter date is in cell C1), just enter the formula =C1+16

Your second sentence doesn't really make sense. Oh, maybe you're saying you want a field to read

12/15/2020--9/30/2021

Now if that's the case, that gets a little tricky but here's how to do it.

=CONCATENATE(TEXT(C3,"mm/dd/yyyy"),"--",TEXT(D3,"mm/dd/yyyy")) 

assuming the first date is in C3, the second in D3.

[#3]

@mclovings 

I didn't understand what exactly you were trying to do.

It would be advantageous if you insert a file (without sensitive data) and explain your plans in detail with regard to this file.

Perhaps these examples will help you solve your problem.

[#4]

@NikolinoDE I would like to use a formula that could use a specific linking factor such as a SSN and would be able to dictate whether or not the dates are continuous without a break and give me a consolidated date. Does this make sense?

[#5]

@mathetes I would like to use a formula that could use a specific linking factor such as a SSN and would be able to dictate whether or not the dates are continuous without a break and give me a consolidated date. Does this make sense?

[#6]

Excuse me, but I'm not a specialist in Social Security Nummbers (SSN).
In the file, please explain in detail what exactly and in which cell what you want to accomplish.
Please keep in mind that we too are ignorant of the knowledge of others.

Should only the days be calculated. from start and end date?
If it is to be linked to SSN, then how?

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)

[#7]

@mclovings 

Not totally. Your spreadsheet gives data, but what would really be helpful is to take that raw data and use it to illustrate what you mean by "continuous without a break" --illustrating it both ways --

The consolidated date is, I'm going to presume unless you correct me, is what I gave you in my first answer. Not really what I'd call a "consolidated date" (it is, after all, two dates) but a single text field that has concatenated (strung together) two separate texts, each of which is a date, with a separator. If that's what you mean, then you have that formula. If it's not what you mean, what do you mean?

[#8]

@mathetes  I attached an update with what I'm seeking to accomplish in column E (showing continuous data). If the previous end date is 02/17/2021 and the new start date is 02/19/2021, then it's not continuous. A gap, or a break is there and it shouldn't calculate. However, if it's continuous, then the new end date replaces the old end date.

[#9]

@NikolinoDE I would like the formula to use a specific linking factor and analyze the start and end dates and if they are concurrent and don't have a day gap. For instance, JANE DOE's end date is 12/31/2020 and her new start date is 01/01/2021, therefore the date is continuous and the old end date of 12/31/2020 is null and void and the new end date is 09/30/2021. The example for Jill Doe has a break after 11/30/2020, therefore I know it's not consecutive, but the next dates are in the realm of the aforementioned scenario.

[#10]

@mclovings 

a) I know the meaning of the word continuous

b) 1/1/21 is not continuous with 12/31/21  (in the Doe, Jane data)

Now, I realize that was a typo on your part; I'm a human who can interpret and discern what was meant from the larger context. A computer, however (until AI gets more developed and implemented in Excel) would NOT know that it was a typo.

Anyway, another question before answering (which will have to come later; I'm going into another meeting in just a few minutes): you show Doe, Jane in contiguous lines. same with doe, Jill. Is that line-by-line alphabetical sort (or sort by SSN) something that your real database will reflect, or will the rows in fact get mixed, so that it could be something like:

Doe, Jill

Doe, Jane

Doe, Jill

Doe, Jil

Doe Jane

Doe, John

[#11]

@mathetes the data is hard data from a system, so I won't be manipulating any numbers. I can sort by name or SSN, but at the moment, I have it sorted by SSN. I won't be mixing them.

[#12]

@mclovings 

Here, first, is a very simple solution that will work if the example you've given is universally true. Which is to say, this will work so long as there are never more than two rows in a row that should be considered continuous dates.

If that's not valid, then we'll have to get potentially a lot more complicated, but I didn't see any reason to do that yet. We'd have to start testing rows for such things as

• count the number of rows where SSN stays consistent
  • (what's the maximum of rows for any given SSN in your data; is there a limit?)
• test each sequence of dates for any given SSN for continuity
  
  • (are they always in date sequence or could continuity be there be rows for the same SSN but separated by an intervening row?)

So I need to ask: do we need to get more sophisticated in potential complications hinted at by those questions? What, if any, limits are there to numbers of rows per SSN, especially to numbers of rows per SSN that could be continuous?