Hi,
In A1:A1000 I have in each cell a text string in the form:
Text / MM/DD/YYYY / Text
An example would be:
John / 07/23/2019 / Smith
The middle 10 characters represents the date in US format.
Can someone please tell me a formula that will convert the date in this string from US (MM/DD/YYYY) to UK (DD/MM/YYYY) form?
Thanks!
Convert or extract?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
You could use Power Query (Get & Transform Data). Format your source data as a table, then use:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Convert, please AliGW.
I was thinking something like putting a formula in each adjacent cell in B1:B1000 to get the data in the required format.
For example, A1 has John / 07/23/2019 / Smith. I would like a formula in B1 to reference A1 and return the text string John / 23/07/2019 / Smith.
Olly - thanks for the suggestion; unfortunately I'm not yet familiar with PQ
That's extracting and converting, not converting in situ. OK - clear now - thanks.I was thinking something like putting a formula in each adjacent cell in B1:B1000 to get the data in the required format.
Now's as good a time as any, to learn...Originally Posted by andrewc
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks