Unified DDMMYY with variable data

[razor_raef]

Hi team,

I'm trying to create a unified formula to give DDMMYY outcome with variable data.

In column A, dates have been pulled from a program which give the formats as attached. Unfortunately the dates are never constant as you can see and trying to create a formula isn't working for me (e.g =TEXT(A1,"DD/MM/YY").

Can anyone please help me in figuring out a simple formula to pull DDMMYY from the variable data?

Thank you in advance

Dates.xlsx
	A
1	8/3/24
2	8/3/24
3	8/3/24 12:00
4	9/3/24
5	9/3/24 12:00
6	9/3/24 12:00
7	10/3/24
8	10/3/24 12:00
9	10/3/24 12:00
10	11/3/24
11	11/3/24 12:00
12	11/3/24 12:00
13	12/3/24
14	12/3/24 12:00
15	12/3/24 12:00
16	13/03/2024 12:00:00 PM
17	14/03/2024 12:00:00 PM
18	15/03/2024 12:00:00 PM
19	15/03/2024 12:00:00 PM
20	17/03/2024 12:00:00 PM
Sheet1

 

[razor_raef]

Really weird, I just posted that with XL2BB and it shows in correct format however the original data ACTUALLY looks like this:

As you can see with the original way it's formatted above, some cells are M/DD/YYYY, others are DD/MM/YYYY, some are M/DD/YY.

I need if possible please Column B to have the formula that produces DD/MM/YY.

 

[Alex Blakenburg]

That seems to indicate that your default system setting for date is m/dd/yyyy is that the case ? In which case why do you want it as dd/mm/yyyy
Does the spreadsheet come to you from somewhere else or is it a spreadsheet you created ?

 

[razor_raef]

That seems to indicate that your default system setting for date is m/dd/yyyy is that the case ? In which case why do you want it as dd/mm/yyyy
Does the spreadsheet come to you from somewhere else or is it a spreadsheet you created ?

The default system that i'm retrieving the information from I don't have accessibilty to checking the date format settings when the report is ran however from the screenshot shown on the actual data cells, it appears some are in MM/DD/YYYY, others are DD/MM/YYYY TIME, MM/DD/YYYY TIME so i'm going to presume my date/time values are going to be completely randomized.

I require to export the information in Column A into Column B as DD/MM/YY for the next part of the reporting run.

The raw data is given to me from a system I only have access to hitting "run report" and it gives me an excel dump of all the information. Every other field is fine but this Column of randomized dates that I can't get to simply show as DD/MM/YY by changing formats because of the different values dumped from the system. I figured if I can copy this Column into a new spreadsheet, run a formula to Column B and export that info back into the original data dump then i'll be fine to go from there.

 

[Alex Blakenburg]

If the initial report is creating an Excel in that same sheet can you go into any cell and type =TODAY()-7 and show me what you see.
Then in a new workbook do the same thing.
Does the system generated workbook return mm/dd/yyyy and the new workbook dd/mm/yyyy ?
If that is the case what happens if you copy from the system generated workbook to the New Workbook using Paste Values ?

By the way: what version of Excel are you using. Can you update your Account Profile ?

 

[razor_raef]

Both show the date as DD/MM/YYYY when entering =TODAY()-7.

Microsoft Excel for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20882) 32-Bit, yes can click into an account profile.

 

[Alex Blakenburg]

Your results look a bit odd to me and I am have some concerns on whether it will refresh in a consistent manner.
Based on what you have at the moment and assuming your data starts in A2, give this a try.

=LET(sDate,IF(ISNUMBER(A2),TEXT(A2,"mm/dd/yyyy"),A2),
DATEVALUE(sDate))

 

[razor_raef]

Hmmm it came back as NAME?

 

[Alex Blakenburg]

What version of Excel are you using ? Can you please update your profile on the Forum to show what version you are using since the answer will vary depending on the version.
Try this without using "LET"

=DATEVALUE(IF(ISNUMBER(A2),TEXT(A2,"mm/dd/yyyy"),A2))

 

[Akuini]

@razor_raef
How about using vba?

Sub flipDate()
Dim x, tx As String
Dim r As Range, va
va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each r In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    tx = Split(r.Text, " ")(0)
    x = Split(tx, "/")
    i = i + 1
    If UBound(x) = 2 Then
        If CLng(x(0)) < 13 Then
            va(i, 1) = DateSerial(x(2), x(0), x(1))
        Else
            va(i, 1) = DateValue(r.Text)
        End If
    Else
        va(i, 1) = Empty
    End If
Next
Range("B1").Resize(UBound(va, 1), 1) = va

End Sub

Book1
	A	B
1	03/08/2024	08/03/2024
2	03/08/2024	08/03/2024
3	03/08/2024 12:00	08/03/2024
4	03/09/2024	09/03/2024
5	03/09/2024 12:00	09/03/2024
6	03/09/2024 12:00	09/03/2024
7	03/10/2024	10/03/2024
8	03/10/2024 12:00	10/03/2024
9	03/10/2024 12:00	10/03/2024
10	03/11/2024	11/03/2024
11	03/11/2024 12:00	11/03/2024
12	03/11/2024 12:00	11/03/2024
13	03/12/2024	12/03/2024
14	03/12/2024 12:00	12/03/2024
15	03/12/2024 12:00	12/03/2024
16	13/03/2024 12:00	13/03/2024
17	14/03/2024 12:00	14/03/2024
18	15/03/2024 12:00	15/03/2024
19	15/03/2024 12:00	15/03/2024
20	17/03/2024 12:00	17/03/2024
Sheet1