Formula to rearrange text in a cell

[Nyx2012]

Hi, I have the following date and time stamp 15/05/2023 11:25:35 which I want to rearrange to the following:
2023-05-15 11:25:35. I have tried the normal format cells route but it does not want to comply.

Please could someone assist?

 

[Peter_SSs]

Welcome to the MrExcel board!

I have tried the normal format cells route but it does not want to comply.

I assume then that the original value is Text and not Date (Numerical). If that is so, you could try this:

23 05 18.xlsm
	A	B
1	15/05/2023 11:25:35	2023-05-15 11:25:35
Rearrange Text

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN("-",,MID(A1,{7,4,1},{4,2,2}))&RIGHT(A1,9)

 

[Peter_SSs]

.. or would it just convert like this?

23 05 18.xlsm
	A	B
1	15/05/2023 11:25:35	2023-05-15 11:25:35
Rearrange Text

Cell Formulas
Range	Formula
B1	B1	=TEXT(A1,"yyyy-mm-dd hh:mm:ss")

 

[Nyx2012]

Hi Peter,

Thank you for your prompt response. I am however getting #NAME? with the provided formula

 

[Peter_SSs]

Hi Peter,

Thank you for your prompt response. I am however getting #NAME? with the provided formula

Your profile lists Office versions including 365. Have you tried in that version?

.. or what about my second suggestion?

 

[Nyx2012]

=TEXT(A1,"yyyy-mm-dd hh:mm:ss")

This one just returns the exact same value in the cell

 

[Nyx2012]

Your profile lists Office versions including 365. Have you tried in that version?

.. or what about my second suggestion?

Hi Peter, I have 2016 running on my server and 365 on my local. Apologies

 

[Peter_SSs]

Hi Peter, I have 2016 running on my server and 365 on my local. Apologies

So have you tried the first formula with 365?

If the original value is in say A1, what does the formula =ISNUMBER(A1) return?

What is your normal date format order, d/m/y or m/d/y?

 

[Alex Blakenburg]

Did you want a date as a result or Text ?
And do you have more dates and do some of the change when you change the date format and not others ?
In B2 you could try the below and the format the cell the way you want to see it.

=IF(ISNUMBER(A2),
DATE(YEAR(A2),DAY(A2),MONTH(A2)),
DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2)))

 

[Nyx2012]

So have you tried the first formula with 365? - Yes but it states there is an error with the formula

If the original value is in say A1, what does the formula =ISNUMBER(A1) return? - FALSE

What is your normal date format order, d/m/y or m/d/y? - yyyy-mm-dd HH:mm:ss