Different date format in a data query

Is there other data in conjunction with the dates?

Ideally you have an identifier to indicate where the date was originated that way you can call out the right value...

IF - the formats stick to what you have shown above... in which the date is MM/DD/YYYY and DD-MM-YYYY then an IF will do just fine...

Is this in a cell alone or is there more data in it?

=IFERROR(DATE(RIGHT(LEFT(A1,FIND(" ",A1)-1),4),RIGHT(LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1),2),SUBSTITUTE(RIGHT(LEFT(A1,FIND("-",A1)-1),2),"[",""))
,DATE(RIGHT(LEFT(A1,FIND(" ",A1)-1),4),SUBSTITUTE(RIGHT(LEFT(A1,FIND("/",A1)-1),2),"[",""),RIGHT(LEFT(A1,FIND("/",A1,FIND("/",A1)+1)-1),2)))

You would need to update all the A1 References to what you want

Welcome to the forum.

For your locale, you will need to change ALL commas to semi-colons:

=HVIS.FEJL(DATO(HØJRE(VENSTRE(A1;FIND(" ";A1)-1);4);HØJRE(VENSTRE(A1;FIND("-";A1;FIND("-";A1)+1)-1);2);UDSKIFT(HØJRE(VENSTRE(A1;FIND("-";A1)-1);2);"[";""));DATO(HØJRE(VENSTRE(A1;FIND(" ";A1)-1);4);UDSKIFT(HØJRE(VENSTRE(A1;FIND("/";A1)-1);2);"[";"");HØJRE(VENSTRE(A1;FIND("/";A1;FIND("/";A1)+1)-1);2)))

If that doesn't work, then there are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Thank you Elegault and AliGW =) (Not sure how to tag both of you)

Now I think I understand the situation. I didn't consider the , vs ; issue. I'll give it a new try and if my limited excel skills still come up short I'll do representative data insert.

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Hi AliGW, thank you the formula works better now (English text and ; ), but it comes up with a #NAME? - not sure why. I've attached a sample worksheet with my data now.
Column J has the various date formats and column K the attempt at a corrected date with a uniform format.

Thanks again and thank you for being patient with me

VBA solution

Function CorrDate(r As Range) As Date
Dim t() As String
t = Split(r, " ")
If InStr(1, r, "/") > 0 Then
    t = Split(t(0), "/")
    CorrDate = DateSerial(t(2), t(0), t(1))
Else
    t = Split(t(0), "-")
    CorrDate = DateSerial(t(2), t(1), t(0))
End If
End Function

in K2

=corrdate([@[Case closed date]])

copy down

To JohnTopley: I've written the formula in K2 and I've added the Developer tool to my ribbon and clicked VBA and then I'm not sure what to do and where to write your solution?

Oh this is much simpler... I thought the brackets you previously used to describe your data was in fact the part of the format.

Just put =DATEVALUE([@[Case closed date]]) as your Date Corrected Column and format to the view/format you wish to utilize. No need for a bunch of extras as fun as it all is

to insert VBA code:

Click on "Developer" >> "Visual Basic"

Click "Insert">> "Module" which will initiate a blank area on the right of the screen

Copy/Paste code into this area then Close window

You are amazing JohnTopley Thank you so much!

Thank you to all of you for helping me! - and for being patient with my excel skills.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Done and Done
Thanks again Ali!

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.