Convert Date from string in "MMM YY" format to YYYYMM format?? 2

[kjv1611]

So, I was trying to skip this portion of a project I'm working on, and using an Input box to capture the right month. But based on my own typo in testing, I figured I'd try to snag the date from the filename instead, and then just have user verify or change if needed.

So... I start with files like this:

"MyFancyFileName-[highlight #FCE94F]APR 15[/highlight] And some other gibberish.xlsx"

I am basically cleaning the data up a bit into a more normalized fashion in a new workbook (consolidating several other workbooks - each workbook for a month). Basically, instead of copying and pasting, b/c of all their variations of what columns are, or are not, included, I'm grabbing the values one at a time, and adding them to a normalized worksheet in a new workbook.

So that said.. all the above is working except for grabbing the month and year correctly from the file name.

Without further ado, here's what I've got so far, and I've got a feeling that surely I've ALMOST got it. I'll be testing on my end in case I don't get a response here.
[CODE VBA]
Dim strMonth As String
Dim wb As Workbook
[green]'Loop through files that are workbooks that I want to include...[/green]

strMonth = Mid(wb.Name, InStr(wb.Name, "-") + 1, 6)
Debug.Print strMonth
[green]'At this point, I get APR 15 in immediate window for a file that has APR 15 in the file name string.[/green]

[highlight #FCE94F]'[green]The following line is where I'm getting the error: Current error is "Run-time error 13: Type mismatch"[/green][/highlight]
strMonth = Month(DateValue(Left(strMonth, 3) & "1, " & Right(strMonth, 4)))
Debug.Print strMonth

[green]'Input box to verify the year and month or whatever I grab is correct[/green]
strMonth = InputBox( _
"Please enter Year and Month of Workbook in YYYYMM format." & vbCrLf & _
"For example, this month is " & Format(Date, "YYYYMM") & ".", "Enter Workbook Month" _
, strMonth) ' Format(DateAdd("m", -1, Date), "yyyymm")[/CODE]

I'm sure the issue is that I'm not getting the date format lined up in a way that Excel can recognize as a date. Can anyone give me any pointers? I'm trying to do this without a case statement for the month abbreviations. I figure there has to be a smarter and shorter way.

Thanks in advance for any help

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

It doesn't seem to be a very common scenario. Usually the starting point is numeric, not a string like this.

I did try adding "20" for assuming the full year, but that didn't help. So that line now reads:

strMonth = Month(DateValue(Left(strMonth, 3) & "1, [highlight #FCE94F]20[/highlight]" & Right(strMonth, 4)))

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[strongm]

What output are you expecting? It is unclear from your current description.

 

[Andrzejek]

If your:[tt]
Debug.Print strMonth[/tt]
gives you: [tt]APR 15[/tt]
and you want: [tt]2015 APR[/tt]

consider:

Dim strMonth As String

strMonth = "APR 15"

MsgBox "20" & Split(strMonth, " ")(1) & " " & Split(strMonth, " ")(0)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[kjv1611]

Sorry, I forgot to be more clear in that portion of the question/post. What I want is:

APR 14 -> 201404
APR 15 -> 201504
JAN 14 -> 201401

YYYYMM format for the output.

I'm using the YYYYMM formatted value as a MonthID field.

 

[TonyJollans]

One way ... replace the line in error with ...

[tt][blue]strMonth = DatePart("yyyy", strMonth) & Right("0" & DatePart("m", strMonth), 2)[/blue][/tt]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Function YYYYMM(dt as string) as string
    YYYYMM = Format(DateValue(Left(dt, 3) & "/01/" & Right(dt, 2) + 2000), "yyyymm")
End Function

 

[kjv1611]

Thanks a ton, Tony!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Wow, you know, this is embarrassing. Tony, I just now realized that your code is somehow adding a 1 to the year??

Skip, your solution works perfect, as always. One of these days, I'll grow up, and maybe I'll be worthy of the name, "Skip".

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Ok... I think I know what the issue with Tony's solution is. Well, it's a guess. My guess is that it assumes 14 is the day # not the year.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[TonyJollans]

Oops! You are correct.

It needs a day as well ...

change .. [tt][blue]strMonth = Mid(wb.Name, InStr(wb.Name, "-") + 1, 6)[/blue][/tt]

to .. [tt][blue]"1 " & strMonth = Mid(wb.Name, InStr(wb.Name, "-") + 1, 6)[/blue][/tt]

or something similar - but Skip's solution looks good - they're all variations on a theme.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Variations on a theme"

That's why I like "Carnival of Venice." I only wish I had the chops.

 

[billyjoemc]

what output are we expected to get from this?

 

[SkipVought]

This" what?

 

[kjv1611]

billyjoemc,

No offense, but if you're just now asking, "what output are we expected to get from this?", alluding to what is the question/reason for the tread, then you did not read it. The question/request was in the title, and I did later clarify it for one thread participant.

TonyJollans,

thanks for clarifying

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57