Trying to trim a value and sort the rest

[MRDBS]

Hello Excel Collective

I have this value from a report, it is a date time format which is not recognized by Excel.
Example: 02-06-2022 22:00

MM-DD-YYYY HH:MM

I tried formatting this and it dis not work in most cases (if possible) it will format it so that the Month is seen as day, if the day nr is to high for month it will not format it.
Example: 02-06-2022 22:00 turns into: 02.06.2022 22:00

So for now I handle this formatted as text and usually do "Text to Column", and it does work.
Sadly in this case i need to collect values from two columns: If no value in Column A show value from Column B.
I do not want to do "Text to Column" on two columns as that over complicates the steps.

The function I am trying to use is:

=DATE(VALUE(RIGHT(LEFT(A2;LEN(A2)-6)*0,4));VALUE(LEFT(LEFT($A2;LEN($A2)-6)*0,2));VALUE(MID(LEFT($A2;LEN($A2)-6);4;2)))

A2 being: 02-06-2022 22:00

The result should be: 06.02.2022 (or 06-02-2022)

Really not sure what else to do.

Best regards.

/Mario

 

[Joe4]

So for now I handle this formatted as text and usually do "Text to Column", and it does work.
Sadly in this case i need to collect values from two columns: If no value in Column A show value from Column B.
I do not want to do "Text to Column" on two columns as that over complicates the steps.

The function I am trying to use is:

=DATE(VALUE(RIGHT(LEFT(A2;LEN(A2)-6)*0,4));VALUE(LEFT(LEFT($A2;LEN($A2)-6)*0,2));VALUE(MID(LEFT($A2;LEN($A2)-6);4;2)))

I would argue that the formula is a whole heck of a lot more complicated then using Text to Columns twice.
But why not simplify it and create a macro that does Text to Columns twice?
Then you can fix it each time with just a few clicks.

 

[MRDBS]

I would argue that the formula is a whole heck of a lot more complicated then using Text to Columns twice.
But why not simplify it and create a macro that does Text to Columns twice?
Then you can fix it each time with just a few clicks.

Well yes I agree the formular is more complex then text to column, but text to column is manual, the formular is not.
This is part of an online Excel file, macros do not work there.

I want to be able to paste A2 into the online sheet and get my result in B2.
This is because several people use that file and I just do not want them to have to do manual work in it.

 

[jtakw]

Hello Excel Collective

I have this value from a report, it is a date time format which is not recognized by Excel.
Example: 02-06-2022 22:00

MM-DD-YYYY HH:MM

Hi,

If it's always that format, would this help, you will probably need to swap the , with ; (comma to semicolon):

Book3.xlsx
	A	B	C
2	02-06-2022 22:00		2/6/2022
Sheet1083

Cell Formulas
Range	Formula
C2	C2	=DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))

 

[MRDBS]

Hi,

If it's always that format, would this help, you will probably need to swap the , with ; (comma to semicolon):

Book3.xlsx
	A	B	C
2	02-06-2022 22:00		2/6/2022
Sheet1083

Cell Formulas
Range	Formula
C2	C2	=DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))

That works!

Thank you for your help.
I would have never guessed I could get away without trimming.

 

[jtakw]

You're welcome, thanks for the feedback.