Forum Discussion
Continuous Date
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?
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?
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.
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