Reformating a Date within a String
I have a very unique request to reformat a date that is within a string.
<RequestedDate Type="DeliverOn">9/12/2016
Needs to read: <RequestedDate Type="DeliverOn">2016-09--
It is a dynamic date.. My thought was something like grabbing the data between >< to work with it.
- data between > and first / = month and has to be in a "MM" fomat with leading 0 < 10
- data between first / and second / = day and needs to be "DD" format with leading 0 < 10
- data between second / and < = year in "YYYY" format
last concatenate year&"-"&month&"-"&day Resulting in a format of YYYY-MM-DD
Is this possible to grab the specific values from the string and name them so they can be used in a concatenation/reformat of the string?
It is not possible to reformat prior to string, because when the .xml is generated it chg's the format on me. Any idea's?
<RequestedDate Type="DeliverOn">9/12/2016
Needs to read: <RequestedDate Type="DeliverOn">2016-09--
It is a dynamic date.. My thought was something like grabbing the data between >< to work with it.
- data between > and first / = month and has to be in a "MM" fomat with leading 0 < 10
- data between first / and second / = day and needs to be "DD" format with leading 0 < 10
- data between second / and < = year in "YYYY" format
last concatenate year&"-"&month&"-"&day Resulting in a format of YYYY-MM-DD
Is this possible to grab the specific values from the string and name them so they can be used in a concatenation/reformat of the string?
It is not possible to reformat prior to string, because when the .xml is generated it chg's the format on me. Any idea's?
Roy Cox
Can you provide an example in a workbook. You would probably need to use a combination of formulas - Mid, Left, Right,.
ASKER
Not really, it is a very busy, very large book with lots of proprietary data. I wanted to make it simple by providing the string. All I am going to do is make active a cell on a sheet and have the reformat run. Sheet tab name is SIF Data and the cell is A22, that part is going to be static, name the cell to get the string and run a reformat. So if you could work with the provided string as it is produced by an external process, that would be great. It will take all the questions about how the string was generated etc. out of the task at hand. Can this be done as explained?
It's not clear from a String whether the date the 9th December or the 12th of September.
Will you have this string in say A1 and a formula can re-format in B1?
Will you have this string in say A1 and a formula can re-format in B1?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thanks, Roy -- although I believe a correction from "ActiveCell.Value = Left(str, 32) & FormatDateFromXMLString(st
ActiveCell.Value =FormatDateFromXMLString(ActiveCell.Value) ASKER
Thank you Roy and Crystal. It did work. I do not understand how to Call to a function as Crystal wrote, but Roy your version (based on hers) is working nicely. Thanks all for the input.
ASKER
All good comments and suggestions. Appreciate the help. Thanks.
you're welcome ~ happy to help
Pleased to help