Excel for Mac Visual Basic - Month() function works but Year() does not?

Question

Excel for Mac Visual Basic - Month() function works but Year() does not?

Brendan J 20

I have written VBA code in Excel for Mac (version 16.106). I'm using the Month(cell_ref) function to return the month number of the date value contained within cell_ref. This works.

But calling Year(cell_ref) on the same data generates a runtime error! Error 13 "Type Mismatch". How can Month() work but Year() not work on the same data?

Is this a bug in Excel VBA? Any suggested work-arounds?

See screenshot of the Watch window in the VBA editor below.

vba

SHEIKH FARIS ZAIGHAM 20 Reputation points

2026-03-02T06:28:28.9033333+00:00
This is not a bug in Excel VBA. The Year() function throws Run-time error 13 (Type Mismatch) when the value passed to it is not recognized as a valid Date type, even if it visually looks like a date in the worksheet.

The reason Month(cell_ref) works while Year(cell_ref) fails is usually because the cell contains a value that can be partially coerced, but isn’t actually stored as a proper Date (for example: text formatted as a date, a Variant subtype mismatch, or a regional parsing issue on Excel for Mac).

Common Causes

The cell contains a string, not a real date.

The value is a Variant/Error.

Regional date format differences (Mac Excel can be sensitive to locale).

The variable passed to Year() is not explicitly typed as Date.

Recommended Fixes

1. Explicitly convert to Date:

Dim d As Date

or directly:

Debug.Print Year(CDate(Range("A1").Value))

2. Check if the value is a valid date first:

If IsDate(Range("A1").Value) Then

3. Force numeric serial value (most reliable workaround):

Debug.Print Year(CLng(Range("A1").Value))

Since Excel stores dates internally as serial numbers, converting to Long often avoids type coercion issues.

Why This Happens

Month() and Year() both expect a Date or something convertible to Date. In some edge cases (especially on Mac), implicit conversion behaves inconsistently depending on the underlying Variant subtype. Explicit casting with CDate() or CLng() removes ambiguity and prevents the type mismatch.

In short: not a VBA bug — just a type coercion issue. Explicit conversion is the safest solution.

If you'd like, I can also give you a deeper explanation of what you're probably seeing in the Watch window.
Brendan J 20 Reputation points

2026-03-02T06:37:30.2133333+00:00

"not a VBA bug — just a type coercion issue. Explicit conversion is the safest solution"

Did you see my comment to the other response? Exhaustive testing shows that VBA agrees that the data type in the referenced cell is of type Date and yet Year() still fails, even when the (already) Date type cell value is coerced into a Date type before passing it to Year().

So it's looking to me like... a bug.

Answer accepted by question author

0 additional answers

Your answer

SHEIKH FARIS ZAIGHAM 20 Reputation points

2026-03-02T06:28:28.9033333+00:00

This is not a bug in Excel VBA. The Year() function throws Run-time error 13 (Type Mismatch) when the value passed to it is not recognized as a valid Date type, even if it visually looks like a date in the worksheet.

The reason Month(cell_ref) works while Year(cell_ref) fails is usually because the cell contains a value that can be partially coerced, but isn’t actually stored as a proper Date (for example: text formatted as a date, a Variant subtype mismatch, or a regional parsing issue on Excel for Mac).

Common Causes

The cell contains a string, not a real date.

The value is a Variant/Error.

Regional date format differences (Mac Excel can be sensitive to locale).

The variable passed to Year() is not explicitly typed as Date.

Recommended Fixes

1. Explicitly convert to Date:

Dim d As Date

or directly:

Debug.Print Year(CDate(Range("A1").Value))

2. Check if the value is a valid date first:

If IsDate(Range("A1").Value) Then

3. Force numeric serial value (most reliable workaround):

Debug.Print Year(CLng(Range("A1").Value))

Since Excel stores dates internally as serial numbers, converting to Long often avoids type coercion issues.

Why This Happens

Month() and Year() both expect a Date or something convertible to Date. In some edge cases (especially on Mac), implicit conversion behaves inconsistently depending on the underlying Variant subtype. Explicit casting with CDate() or CLng() removes ambiguity and prevents the type mismatch.

In short: not a VBA bug — just a type coercion issue. Explicit conversion is the safest solution.

If you'd like, I can also give you a deeper explanation of what you're probably seeing in the Watch window.
Brendan J 20 Reputation points

2026-03-02T06:37:30.2133333+00:00

"not a VBA bug — just a type coercion issue. Explicit conversion is the safest solution"

Did you see my comment to the other response? Exhaustive testing shows that VBA agrees that the data type in the referenced cell is of type Date and yet Year() still fails, even when the (already) Date type cell value is coerced into a Date type before passing it to Year().

So it's looking to me like... a bug.

Answer 1

Marcin Policht 81,790 MVP Volunteer Moderator

This is not a bug in Year(). It looks like it's a type issue.

From your Watch window, CleanDataSheet.Cells(Pstart, 1) and CleanDataSheet.Cells(i + 1, 1) are shown as Variant/String, not as Date. Even though they display as 1/1/2024 and 1/2/2024, they are text values that look like dates.

Month() is more tolerant and will implicitly coerce certain date-looking strings to a date. Year() is stricter on Mac VBA and in some regional settings will throw Run-time error 13 if the string cannot be safely coerced to a Date.

So what is happening is implicit conversion working in one case and failing in the other.

The correct fix is to explicitly convert the value to a Date before calling Year or Month.

Use:

Year(CDate(CleanDataSheet.Cells(Pstart, 1).Value))

and

Year(CDate(CleanDataSheet.Cells(i + 1, 1).Value))

or:

If IsDate(CleanDataSheet.Cells(Pstart, 1).Value) Then
    y = Year(CDate(CleanDataSheet.Cells(Pstart, 1).Value))
End If

A likely even better solution would be to ensure the cells contain real dates, not text. You can check this:

Debug.Print TypeName(CleanDataSheet.Cells(Pstart, 1).Value)

If it prints String, they are text. If it prints Date or Double, they are real Excel dates.

To force conversion in the sheet you can use:

CleanDataSheet.Columns(1).TextToColumns Destination:=CleanDataSheet.Range("A1"), DataType:=xlDelimited

or multiply by 1 in a helper column.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Brendan J 20 Reputation points

2026-03-02T05:13:25.7833333+00:00
Thanks for that prompt response. This appears to be a very odd bug.

I tried your suggestions, with a couple of extra lines:

Debug.Print TypeName(CleanDataSheet.Cells(Pstart, 1).Value) If IsDate(CleanDataSheet.Cells(Pstart, 1).Value) Then MsgBox ("Is Date") y = Year(CDate(CleanDataSheet.Cells(Pstart, 1).Value)) MsgBox (y) End If

The code agrees the contents of the cell referenced is of type Date . The Debug line prints "Date" to the Immediate Window, and the code then displays the message box saying "Is Date".

Clicking "OK" on that message box then generates runtime error 13 "Type Mismatch". Clicking "Debug" on that error dialog box takes me back to the code in the VBA Editor highlighting the line on which the error occurred:

So VBA agrees the referenced cell value is of type Date, but it then refuses to return the year of that date.

The referenced cells contains a date and time using the format d/m/yyyy hh:mm if that makes any difference (it shouldn't).
Marcin Policht 81,790 Reputation points MVP Volunteer Moderator

2026-03-02T12:05:47.4933333+00:00
If TypeName(...) prints Date and IsDate is True, then indeed the problem does not appear to be the cell value. My guess is that the error is caused by something shadowing the Year function.

In VBA you can accidentally override built-in functions by declaring a variable, function, or property named Year. When that happens, Year(...) no longer refers to the VBA date function and you get a type mismatch when it tries to treat your Date as something else.

Check your project for something like:

Dim Year As ...

or

Public Year As ...

or a Function/Sub named Year.

To test, you can also bypass any shadowing by fully qualifying the VBA function:

y = VBA.Year(CDate(CleanDataSheet.Cells(Pstart, 1).Value))

If that works, you have a naming conflict. Rename your variable/function (for example yr instead of Year) and the problem will disappear.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Brendan J 20 Reputation points

2026-03-03T05:56:55.76+00:00

Thanks Marcin... that was exactly the problem. It never occurred to me to check that, since I'm used to programming languages where using a builtin function name as the name of a variable would cause a compilation error. I now know VBA does not do that!

Share via

Excel for Mac Visual Basic - Month() function works but Year() does not?

Common Causes

Recommended Fixes

Why This Happens

0 additional answers

Your answer