asked on # Changing date format in row one

Hi,

I get emailed a workbook each week with updated figures that we have to ship to the customer.

Problem is, that the dates in row 1 have the following format '141112' etc etc. meaning 12/11/14 and so on.

Is there a formula or maybe vba code that I could use that would change the dates around to read 12/11/14 etc instead of 141112 ?

I have attached the workbook in question.

Your help as always is much appreciated.

wk-46.xlsx

I get emailed a workbook each week with updated figures that we have to ship to the customer.

Problem is, that the dates in row 1 have the following format '141112' etc etc. meaning 12/11/14 and so on.

Is there a formula or maybe vba code that I could use that would change the dates around to read 12/11/14 etc instead of 141112 ?

I have attached the workbook in question.

Your help as always is much appreciated.

wk-46.xlsx

Microsoft Excel

Is that format YYMMDD? If so, use this formula...

=DATE(LEFT(A1, 2)+100, MID(A1, 3, 2), RIGHT(A1, 2))

...where A1 contains your date.

=DATE(LEFT(A1, 2)+100, MID(A1, 3, 2), RIGHT(A1, 2))

...where A1 contains your date.

If it's VBA you are after, select the cells to modify then run this macro...

```
Sub ChangeDateFormat()
Dim cell As Range
For Each cell In Selection
If Len(cell) = 6 Then
cell = DateSerial(Left(cell, 2), Mid(cell, 3, 2), Right(cell, 2))
End If
Next
End Sub
```

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

So the month is the last 2 digits on the right?

Does that make sense for column F value 141113? That would be month 13, or Jan 11, 2015? Is that what you are saying?

Also what is with 141201-49-W "date" starting in Column X? Thanks.

Does that make sense for column F value 141113? That would be month 13, or Jan 11, 2015? Is that what you are saying?

Also what is with 141201-49-W "date" starting in Column X? Thanks.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Thanks a lot for all the comments, I will try them out when I get to work. Regarding Tom's question about " ...Also what is with 141201-49-W "date" starting in Column X?" The week number gets added once the dates get so far out. And I should have pointed out that the dates are European not American so the last two digits of the block of six are the days and the middle two are the month

Thanks a lot to all that contributed, but Kris gets the gold star. I can 'trim' the cells that contain week numbers before I run the macro.

Thanks again for your time

Thanks again for your time

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

This works for columns E to W only:

Open your xls.

Save as .txt.

Open the .txt. The Import wizard should start.

Pick Comma delimiter.

Change Column data format to YMD.

Scroll to right and select columns E to W.

Finish.