Format() function in VBA not formatting dates correctly

Question

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!

It interprets the 15 and 16 as days and defaults to 2016 as year. For your specific format "20" & Right(WS.Name,2) would work. — arcadeprecinct
– arcadeprecinct, Commented Jul 7, 2016 at 13:34

simon at rcl · Accepted Answer · 2016-07-07 13:36:15Z

1

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.

answered Jul 7, 2016 at 13:36

simon at rcl

7,3691 gold badge21 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kranberry Over a year ago

The funny thing is, I did think of what you're saying -- that "May-16" isn't a date. So to test this use of the Format() function, I explicitly gave it a few "mmm-yy" strings (of different years), and each time in those tests it did correctly identify the year and output it. Which is why I can't understand why it fails only when it is passed the WS.name string.

kranberry Over a year ago

UPDATE: I used part of your suggestion. I prefixed "01-" to WS.name before passing it, and it seems to work perfectly now. Thanks for that. I guess the problem then was the fact that "May-16" isn't a date, and this is perhaps where @arcadeprecinct 's suggestion that it was interpreting the "yy" as days, comes into play. Either way, though, it still doesn't make sense why it worked in the tests (as I explain in the comment above) and not when passed the WS.name string. If you figure it out, let me know. Anyway, thanks to both of you.

Ghost_Rider · Accepted Answer · 2016-07-07 14:04:13Z

0

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

answered Jul 7, 2016 at 14:04

Ghost_Rider

12 bronze badges

1 Comment

arcadeprecinct Over a year ago

You are converting the string yyyySplit(1) to integer only to have in converted back to a string. In my opinion using Right(WS.Name,2) would be a better choice because it isn't necessary to create a string array. Also it could handle any format where the year is on the right (two-digit and four-digit year as well as different delimeters).

Collectives™ on Stack Overflow

Format() function in VBA not formatting dates correctly

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related