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

NVIT

Are row 1 items from columns X and on also dates?

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.

Wayne Taylor (webtubbs)

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.

Wayne Taylor (webtubbs)

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

Open in new window

Tom Farrar

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.

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Stephen Byrom

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

Stephen Byrom

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