Stephen Byrom

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

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
```

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.

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

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

ASKER

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

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.