1

I have some cells in my spreadsheet that have multiple dates separated by a semicolon ;.

After some googling, I found that TEXTSPLIT is probably the best formula to break this apart, but it produces several columns of data. I'm hoping to compare the values within the cell before they are broken apart and return one value that is the greatest in the group.

Is this possible? One thing I just thought of is to have a bit of a convoluted machine that breaks out values in a separate sheet and another formula that takes the max value from those rows. Would like to avoid using multiple sheets if I can.

Here is the example: TextSplit formula example

Improve this question
8
  • 1
    Try this way: =MAX(TEXTSPLIT(A1,";")/1) where cell A1 refers to the cell reference where the dates are delimited using semicolon
    – Mayukh Bhattacharya
    Commented Feb 20 at 2:52
  • Thanks for replying. Unfortunately that is not working. When I attempt to run just =MAX(TEXTSPLIT(A1, ";") it returns a value of 0. Adding the '/1' does not improve that.
    – Beer_en_thu_si_asT
    Commented Feb 20 at 2:55
  • 1
    Hey there, could you update your post with some sample dates as text so i copy and paste it in the excel to run a test
    – Mayukh Bhattacharya
    Commented Feb 20 at 2:56
  • 1
    it seems those are formatted as text or they are having some white spaces you need to clean them based on the characters they have, since i dont see any sample data on the post, hence guessing it as white spaces then use TRIM() if not then special characters usually and common one, SUBSTITUTE(A1,CHAR(160),)+0
    – Mayukh Bhattacharya
    Commented Feb 20 at 3:00
  • 1
    Sir all I agree with you. But there should be rule irrespective of any. The rule should be for everyone. Whatever you have said is right, I am not at all disagreeing you, but I have known the stack community from very long, and i still see people are doing that, very few asks, them. I can point many questions here, where questions are not properly asked yet they got solutions, even sometimes solutions are wrong still they got accepted.
    – Mayukh Bhattacharya
    Commented Feb 20 at 3:30

2 Answers 2

Reset to default
2

If I have understood correctly, then the following formula should work:

enter image description here

  • Formula used in cell B1
=MAX(--TEXTSPLIT(A1,"; "))

Or Using BYROW() to get all the output as once, without fill down!

enter image description here

=BYROW(A1:A3,LAMBDA(α, MAX(--TEXTSPLIT(α,"; "))))

With the existing sample data the above formula works as well, and here is a sample demonstration.

enter image description here

Explanations:

  • Using TEXTSPLIT() function: it helps to split the strings by the delimiter semi-colon with a space --> ; across columns. (Please refer the .gif it shows).

Since its a function that falls under the function library Text hence one can realize that when used it will split the strings of dates as text, which creates a problem for future data manipulations, (Note: In Excel dates and times are stored as numbers, therefore the integer part represents the dates while the decimal is the time for the date.)

Hence as mentioned in OP by user using DATEVALUE() it can be converted to true dates or using double unary or by adding 0 or by multiplying by 1 or diving by 1, which eventually converts.

  • Once the above is done, then we can wrap it within MAX() function to return latest/greatest/max/highest (whichever suits in saying) date.

Reasons:

Why User was getting 0 while using the formula provided by me, while they were getting the respective output using DATEVALUE(), to explain, the screenshot clarifies it.

enter image description here

  • User was using delimiter only semicolon ; while I used semicolon and a space ;
  • So when when wrapping within MAX() with the double unary it was returning an error, as it was only converting the first one to a number while the second one was returning an error as it had a leading space. But the one with delimiter semicolon and space should work, refer screenshots.

enter image description here

  • Using DATEVALUE() function it takes care of the leading space and converts the same to a number and lastly wrapping within MAX() gives the desired output. Alternative ways, are has been mentioned in the beginning using ; , and another way to deal with it is using TRIM()
=MAX(--TRIM(TEXTSPLIT(A3,";")))
Improve this answer
2
  • 1
    Thank you. What is the -- for in front of the TEXTSPLIT formula?
    – Beer_en_thu_si_asT
    Commented Feb 20 at 3:18
  • 2
    It means -- double unary it converts numbers formatted as text to actual/true numbers that excel understand, in excel date/time are stored as numbers, so using -- or +0 or /1 or *1 it converts text formatted dates to true dates.
    – Mayukh Bhattacharya
    Commented Feb 20 at 3:19
1

I have not tried the answer provided by @MayukhBhattacharya, but I do appreciate the information provided, so I gave the answer to that user.

I determined that the answers needed to be changed to a DateValue, so I inserted the DATEVALUE formula between the MAX and TEXTSPLIT formulas.

The formula I used was:

=MAX(DATEVALUE(TEXTSLIT(A2, ";")))

See example below:

Incert DATEVALUE into formula

Be sure to format the cell using the short date field.

Improve this answer
4
  • 1
    DATEVALUE is not required, for any user around world using Excel for every dates/times are stored as numbers in Excel. The Integer part is the date while the decimal part is the time. So, when you are using text functions like TEXTSPLIT() to split, it formats the dates as text, to convert back them to numbers, you can use DATEVALUE() or a shorter way is of using -- double unary or the other ways as explained in earlier comments. and then wrap within the aggregate functions like MIN() or MAX() to get desired output. To make it shorter i have used double unary
    – Mayukh Bhattacharya
    Commented Feb 20 at 3:46
  • 1
    Thank you for clarifying. I posted the answer because this is how I came to resolve this issue. It's probably not as slick nor is it using best practices which I'm sure yours is, but thought it might be a good/simple answer for beginners like myself. Thank you again for your feedback!
    – Beer_en_thu_si_asT
    Commented Feb 20 at 3:48
  • 1
    Ok, I have realized one thing, the DATEVALUE() helps in converting the text into actul dates here while double unary did for the first instance, this is because in your formula the delimiter you have used is ; only semicolon, while i have used ; semicolon and the following space it carries. Also wrapping TRIM() would remove the leading space. =MAX(--TRIM(TEXTSPLIT(A2,";")))
    – Mayukh Bhattacharya
    Commented Feb 20 at 4:28
  • 1
    Very cool, thank you for the clarification @Mayukh Bhattacharya
    – Beer_en_thu_si_asT
    Commented Feb 20 at 5:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .