Feb 19 2021 11:52 AM - edited Feb 19 2021 12:27 PM
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.
Feb 19 2021 12:17 PM
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.
Feb 19 2021 12:18 PM
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.
Feb 19 2021 12:25 PM
@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?
Feb 19 2021 12:25 PM
@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?
Feb 19 2021 12:35 PM
Feb 19 2021 12:35 PM
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?
Feb 19 2021 12:40 PM
@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.
Feb 19 2021 12:44 PM
@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.
Feb 19 2021 12:50 PM - edited Feb 19 2021 12:52 PM
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
Feb 19 2021 12:57 PM
@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.
Feb 19 2021 04:04 PM - edited Feb 19 2021 04:07 PM
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
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?