Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reformating a Date within a String

Posted on 2016-09-09
12
Medium Priority
?
58 Views
Last Modified: 2016-09-09
I have a very unique request to reformat a date that is within a string.

<RequestedDate Type="DeliverOn">9/12/2016</RequestedDate>

Needs to read: <RequestedDate Type="DeliverOn">2016-09--12</RequestedDate>

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?
0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41791477
Can you provide an example in a workbook. You would probably need to use a combination of formulas - Mid, Left, Right,.
0
 

Author Comment

by:RWayneH
ID: 41791505
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?
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41791511
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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 332 total points
ID: 41791539
Function FormatDateFromXMLString(pString As String) As String
'160909 strive4peace
'returns a STRING not a date
'pString example: <RequestedDate Type="DeliverOn">9/12/2016</RequestedDate>
'return: "2016-09-12"
   On Error GoTo Proc_Err
   Dim iPos As Integer _
      , iPos2 As Integer _
      , sDate As String _
      , dDate As Date
   'initialize return value
   FormatDateFromXMLString = ""
   'look for >
   iPos = InStr(pString, ">")
   If iPos > 0 Then
      'look for <
      iPos2 = InStr(iPos + 1, pString, "<")
      If iPos2 > 0 Then
         'get what is between them
         sDate = Mid(pString, iPos + 1, iPos2 - iPos - 1)
         'convert to a date
         dDate = CDate(sDate)
         'format the date
         FormatDateFromXMLString = Format(dDate, "yyyy-mm-dd")
      End If
   End If
Proc_Exit:
   On Error Resume Next
   Exit Function
Proc_Err:
   Resume Proc_Exit
End Function

Open in new window

0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 332 total points
ID: 41791619
Monster of a formula

=REPLACE(A1,FIND(">",A1,FIND(">",A1,FIND("RequestedDate",A1)))+1,9,RIGHT(SUBSTITUTE(REPLACE(REPLACE(A1,1,FIND(">",A1,FIND(">",A1,FIND("RequestedDate",A1))),""),FIND("<",REPLACE(A1,1,FIND(">",A1,FIND(">",A1,FIND("RequestedDate",A1))),"")),9999,""),"/","-"),4)&"-"&LEFT(SUBSTITUTE(REPLACE(REPLACE(A1,1,FIND(">",A1,FIND(">",A1,FIND("RequestedDate",A1))),""),FIND("<",REPLACE(A1,1,FIND(">",A1,FIND(">",A1,FIND("RequestedDate",A1))),"")),9999,""),"/","-"),LEN(SUBSTITUTE(REPLACE(REPLACE(A1,1,FIND(">",A1,FIND(">",A1,FIND("RequestedDate",A1))),""),FIND("<",REPLACE(A1,1,FIND(">",A1,FIND(">",A1,FIND("RequestedDate",A1))),"")),9999,""),"/","-"))-5))
0
 
LVL 23

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 332 total points
ID: 41791646
The formula could be a bit shorter
=LEFT(A1,FIND(">",A1))&TEXT(DATEVALUE(A2),"yyyy-mm-dd")&RIGHT(A1,LEN(A1)-FIND("<",A1,2)+1)

Open in new window

Or if the system settings does not recognize US date format (like mine) this
=LEFT(A1,FIND(">",A1))&TEXT(DATE(MID(A1,FIND("<",A1,2)-4,4),MID(A1,FIND(">",A1)+1,FIND("/",A1)-FIND(">",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)),"yyyy-mm--dd")&RIGHT(A1,LEN(A1)-FIND("<",A1,2)+1)

Open in new window

0
 
LVL 22

Accepted Solution

by:
Roy Cox earned 1004 total points
ID: 41791670
Reading the question, I think the OP wants to format the existing text. This would require VBA. The Function provide by Crystal would work like this

Option Explicit

Sub ReFormat()
Dim str As String

str = ActiveCell.Value
ActiveCell.Value = Left(str, 32) & FormatDateFromXMLString(str) & Right(str, 16)
End Sub
Function FormatDateFromXMLString(pString As String) As String
'160909 strive4peace
'returns a STRING not a date
   Dim iPos As Integer _
      , iPos2 As Integer _
      , sDate As String _
      , dDate As Date
   'initialize return value
   FormatDateFromXMLString = ""
   'look for >
   iPos = InStr(pString, ">")
   If iPos > 0 Then
      'look for <
      iPos2 = InStr(iPos + 1, pString, "<")
      If iPos2 > 0 Then
         'get what is between them
         sDate = Mid(pString, iPos + 1, iPos2 - iPos - 1)
         'convert to a date
         dDate = CDate(sDate)
         'format the date
         FormatDateFromXMLString = Format(dDate, "yyyy-mm-dd")
      End If
   End If
End Function

Open in new window

0
 
LVL 22
ID: 41791731
thanks, Roy -- although I believe a correction from "ActiveCell.Value = Left(str, 32) & FormatDateFromXMLString(str) & Right(str, 16)" to:
ActiveCell.Value =FormatDateFromXMLString(ActiveCell.Value) 

Open in new window

provided that it is desired to convert whatever was in the activecell XML to the date ... however the previous and susequent lines could also contain XML so perhaps it would be better to put formula somewhere else and send a cell reference.  I am busy right now so perhaps you can help with that too? thanks
0
 

Author Comment

by:RWayneH
ID: 41791750
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.
0
 

Author Closing Comment

by:RWayneH
ID: 41791757
All good comments and suggestions.  Appreciate the help.  Thanks.
0
 
LVL 22
ID: 41791790
you're welcome ~ happy to help
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41792264
Pleased to help
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question