Avatar of Stephen Byrom
Stephen Byrom
Flag for Ireland 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
Microsoft Excel

Avatar of undefined
Last Comment
Stephen Byrom

8/22/2022 - Mon
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

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

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
Sign up - Free for 7 days
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
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.