CODEWITHSUNDEEP
vbaexcel
kranberry
kranberry

Reputation: 364

Format() function in VBA not formatting dates correctly

I'm having trouble using the Format() function in VBA for Excel. I have not the least idea what the problem is. I've used the Format() function in tonnes of places before and never has it given trouble like this.

For context, let me briefly explain the situation. I have a bunch of worksheets, all named with a month-year combination in the "mmm-yy" format (except the "Main Sheet" sheet). In the function below (simplified for relevance), my aim is to cycle through each sheet, and display it's name in the "yyyy" format. As you can see, in line 7, I'm using the Format() function to convert the worksheet name to "yyyy" format. 

Function populateYearsCBB()
    Dim WS As Worksheet
    Dim yyyyName As Integer
    For Each WS In ThisWorkbook.Worksheets
        If WS.name <> "Main Sheet" Then
            'WS.name is therefore a month-year combo of "mmm-yy" format
            yyyyName = Format(WS.name, "yyyy")     'LINE 7
            MsgBox (yyyyName)
        End If
    Next
End Function

A little more background: My first 5 sheets are the first five months of 2016 (May-16, Apr-16, Mar-16...) and the remaining 12 are the 12 months of 2015 (Dec-15, Nov-15...).

Of course, when I run this function, I would expect my message-boxes' outputs to be: "2016", "2016", "2016", "2016", "2016" (for the five months of 2016), and then 12 times "2015". Right?

Oddly, I get 17 message boxes all saying "2016".

For some reason, the Format() function on line 7 seems to be reading the 2015 months as 2016 as well.

I can't for the life of me figure out what's going on here. Someone please help?

Thanks in advance!

Upvotes: 0

Views: 859

Answers (2)

Ghost_Rider
Ghost_Rider

Reputation: 1

Assuming, as you said, you keep your formatting the same:

Function populateYearsCBB()
Dim WS As Worksheet
Dim yyyyName As Integer
Dim yyyySplit() As String

For Each WS In ThisWorkbook.Worksheets
    If WS.name <> "Main Sheet" Then
        yyyySplit = Split(WS.name, "-")
        yyyyName = 20 & CInt(yyyySplit(1))
        'WS.name is therefore a month-year combo of "mmm-yy" format
        'yyyyName = Format(WS.name, "yyyy")     'LINE 7
        'you can change the name here using WS.name = yyyyName
        MsgBox (yyyyName)
    End If
Next
End Function

Upvotes: 0

simon at rcl
simon at rcl

Reputation: 7344

The reason that this fails is that 'May-16' isn't a date. What you can do is to make it into one, convert it to a Date type, and then format is. Your code between the if and the end if would then be:

Dim aDate as Date
aDate = CDate("01-" + ws.name) ' e.g. '01-May-2015' which is a real date
yyyyName = Format$(aDate, "yyyy")

That should do it.

Upvotes: 1

Related Questions