Format in date

[abraham30]

Hello everyone,

In my attached sheet, I want to convert the date from dd-mon-yyyy to yyyy/mon/dd format.
(irrespective of the othere data attached to it such as ~)
Sometimes, the date is available only in mon-yyyy format (e.g. OCT-1984 in row number 10) --> I want to convert this into 1984/OCT/--
Sometimes, the date is available only in yyyy format (e.g. 2013 in row number 13) --> I want to convert this into 2013/---/--
Please note that whereever there is a missing day or month or year, it will be filled with hyphen symbol (-)

I already asked the question in a forum but did not get any answer. can anyone help me.
Advance thanks

[streub]

You probably have not received a response because more information is needed. You don't mention how the data is obtained or why you have it entered in the manner it is. I am not an expert but I would need to know.

[abraham30]

My req is to convert years from dd-mon-yyyy format to yyyy/mm/dd format only wherever it is there.
Where ever value for day or month or year is not available, that will be filled with hyphen symbol(-)

See the output which require in column B in newly attached sheet

[streub]

This is a great project for me to learn from but that doesn't help you immediately. I was able to write a macro that will split the text to columns and format the dates as required. I believe you need to "split", format, and concatenate but that is beyond my skill level. Have you considered a column for each date with a formula to determine the number of days? Formating the date with a custom "YYYY/MMM/DD" would be easier. I guess it depends upon how much data you have to change. Also, having "~days" in the same cell as a date makes it difficult.

[EirikDaude]

What looks like the "easiest" solution to me, is to use a regex to sort out the parts of the strings which matches the different formats the date can have and then use replace to put stuff back into the string in the correct format. Unfortunately I have minimal experience with regexes, and I don't really feel like starting to read up on it right now either.

So hopefully streub's method will either work, or he'll find some other method to solve the issue :P Good luck!

[Kenneth Hobs]

No one can help as you have many rules and those rules are not strict. For example:
1. A1 first date string is: 01-JAN-2013. B1 converted string is: 2013/01/01.
2. A2 first date string is: 01-JAN-2013. B2 converted string is: 01/01/2013.

Obviously, B1's first part should equal B2's first part since the input date string for those are the same.

Coding is logically and if the logic (rules) can not be determined, then no solution is possible.

When cross-posting, as a courtesy, you should post the link to the other forum. http://www.excelguru.ca/node/7

Try making your example much more simple with just 1-2 dates and example conversions that are consistent and logically. Saying what you want can be better than coding. Both is best. The key is for you to make us see what you are thinking which must be logical. A simple table of input and output for each case scenario might be worthwhile.

This is why you received no replies in the other thread, maybe, and no solution here. There are many date scenarios and the rules are not clear.

I can show you how to use Split() and convert date strings to dates and then Format to one way. When I get time, I will see if I can do part to get you started. Obviously, there will be many cases to handle to get to your full solution.

[abraham30]

Please find the formula in newly attached sheet where the date format has been converted.
But problem is that if a cell contain more than 1 values, then it is not working e.g. cell(2,1) --> formula not working
some cell which does not contain any record, the text are automatically deleted e.g. R6, R9, R11 etc

As I am not an expert and trying to find solution through google and some other forum, is it possible to develop one macro which restrict the above problem.


Your work will be highly appreciated

[Kenneth Hobs]

I finally got time to show the Split() example that I promised.

Your need is so custom, I doubt that any google search would help. There are parts though that could help if you knew what they were and how to put them together.

My code demonstrates two methods to use the main function, ParseDates(). Those methods are by a Test Sub and as a UDF. While I may not have handled all of your cases or scenarios, this should get your going. Notice that it is modular which will make it easy to understand. Most all of the routine functions are very similar. The real work is done in the last function.

Put this code in a Module. Type =ParseDates(A2) in B2, and fill down.


Sub Test_ParseDates()
MsgBox ParseDates(Range("A7"))
End Sub

'=parsedates(A7)
Function ParseDates(r As Range) As String
Dim i As Integer, a() As String
Application.Volatile False
a() = Split(r.Value2, vbLf)
For i = 0 To UBound(a)
a(i) = ParseDates2(a(i))
Next i
ParseDates = Join(a, vbLf)
End Function

Function ParseDates2(s As String) As String
Dim i As Integer, a() As String
a() = Split(s, ";")
For i = 0 To UBound(a)
a(i) = ParseDates3(a(i))
Next i
ParseDates2 = Join(a, ";")
End Function

Function ParseDates3(s As String) As String
Dim i As Integer, a() As String
a() = Split(s, "~")
For i = 0 To UBound(a)
a(i) = ParseDates4(a(i))
Next i
ParseDates3 = Join(a, "~")
End Function

Function ParseDates4(s As String) As String
Dim i As Integer, a() As String
a() = Split(s, "(")
For i = 0 To UBound(a)
Select Case True
Case Len(a(i)) = 11 'dd-mmm-yyyy"
If InStr(a(i), "-") > 0 Then _
a(i) = Format(CDate(a(i)), "yyyy/mm/dd")
Case Len(a(i)) = 8 'mmm-yyyy"
If InStr(a(i), "-") > 0 Then _
a(i) = Format(CDate(a(i)), "yyyy/mm/--")
Case Len(a(i)) = 4 'yyyy"
a(i) = a(i) & "/--/--"
Case Else
End Select
Next i
ParseDates4 = Join(a, "(")
End Function

[abraham30]

Words can never express my gratitude, I am forever grateful for what you did.
Its working perfectly.

[abraham30]

Sorry to bug you once again Kenneth..
I really appreciate you help that you have provided me for my problem.
In my attached sheet, when I tested the function, it is not working for few dates present in the middle. My sincere request to look into the matter once again if possible.
e.g. in row number 2 for date 01-NOV-2013
in row number 3 for date 01-JAN-2013

(I have modified the function by adding time which is working)

Advance thanks

[abraham30]

Apologize, I am able to solve finally. Due to some extra spaces, it was not working