Change Date Format

[mma3824]

Hello,

I need to be able to change the date format from DD/MM/YY to MM/DD/YY. In the past I would have used a Mid&left&right formula. The issue that I am having is that the column has dates of 3/4/14 and 25/11/12 in the same column so I am unable to use the same Mid&left&right formula because of the extra spaces.

Any thoughts on how to rearrange these dates would be a great help.

Thank you

[Olly]

Would it not be simpler to change the numberformat of the cells, to "mm/dd/yy" ?

[mma3824]

The issue is that I am exporting data from other data systems and a formula would simplify the processes.

[oeldere]

@mma3824

how, with an macro?

if so, add the given solution to the macro.

if not, you could select the whole column and change the format of the whole column at once.

[mma3824]

Format Date.xlsx

I have attached the document that I am working with. The columns that I need formatted are columns I and S.

Thank you.

[oeldere]

J6= trim(I6)*1

and format as mm/dd/yy

See the attache file.

I added an helpcolumn in column J.
You can add iferror on the formula to prefend the failure.

j6=iferror(trim(I6)*1,"")

[mma3824]

OK, it is now adding a zero in front of the single digit day. If I am following I now need to run the Mid&left&right format to take from the International date format to the US date format? Because I'm not seeing the date format change from the DD/MM/YY format.

[ben_hensel]

The issue is that I am exporting data from other data systems and a formula would simplify the processes.

Lemmee clarify something here:
1) Are you exporting data FROM another data system TO excel,
2) Or are you taking data FROM excel and exporting it TO another system?

If (1), select the column (I for example), then Data Ribbon => Data Tools Panel => Text-to-Columns Button
Next => Next => Change "column data format" from "General" to "Date", and change the dropdown to "MDY" => Finish

Repeat on column S

You're done.

If it's (2),
Then it depends on what system you're exporting to: if it's still something in the Microsoft garden (eg, an Access database or something), then I would suggest doing exactly the same thing.

Moving your dates to the daycode serial number and then changing the format to display how you want it to look is probably better for you.

Well, even if you need to output a text string, using the serial code as a helper column would let you simply concatenate, for example:

T3 = DATEVALUE(S3)
U3 =TEXT(MONTH(T3),"00") &"/"& TEXT(DAY(T3),"00") &"/"& RIGHT(YEAR(T3),2)

then pull down

[mma3824]

The first one worked. Great!!! Thank you!

[oeldere]

Please Login or Register  to view this content.

You have several reply on your question.

In that case it is usefull to add to who you are reply-ing.

If it is not me, what about the solution in #6.