VBA & Macro Problems

How to Fix Excel VBA DateSerial and Incorrect Weekday Formatting

Rana Garcia Sep 25, 2026 869 views

Question details

The user needs to automate date formatting across 365 worksheets using VBA, but applying the Year function to a numeric value yields incorrect years and weekdays.

How to Fix Excel VBA DateSerial and Incorrect Weekday Formatting

Product: Excel
Device & OS: not provided
Scenario: Using VBA to automate the creation and date-formatting of 365 daily worksheets where dates must display as 'Mmm dd yyyy (Ddd)'.
Observed behavior: Using a fixed 4-digit number inside the Year() function (e.g., Year(2025)) treats the number as a date serial, resulting in the year 1905 and incorrectly formatted weekdays.

Before you start

Ensure the Developer tab is enabled in your ribbon to access the VBA Editor, and verify the specific text format string (like 'mmm dd yyyy (ddd)') you want applied to your worksheets.

Solution 1Recommended

Use Direct Numeric Values in DateSerial and Format Strings

Avoid passing a 4-digit number into the Year() function, and use DateSerial alongside the Format function to generate correct date strings in VBA.

When dealing with VBA dates, the Year() function is designed to extract a year from a valid date serial, not to process a 4-digit number. Passing '2025' directly to DateSerial prevents the system from misinterpreting your input.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications window, and locate the module containing your worksheet automation macro.

Remove Year() Functions on Numbers

Find instances in your code where Year(2025) is used. Delete the Year() wrapper and use the number 2025 directly inside the DateSerial function, like DateSerial(2025, mth, dy - 1).

Apply the Format Function

Assign the date variable using the Format function to achieve your desired text layout. Example: strCellDate = Format(DateSerial(2025, mth, dy - 1), "mmm dd yyyy (ddd)").

Remove Unnecessary Arguments

Delete any first-day-of-week arguments from your formatting code. The "ddd" format string handles weekdays automatically based on the underlying date serial.

Use Direct Numeric Values in DateSerial and Format Strings

Why Year(2025) returns 1905: VBA date serials start on December 30, 1899. If you pass '2025' into the Year() function, VBA interprets it as the 2,025th day in its calendar system, which lands in the year 1905.

Solution 2

Output Actual Dates and Apply Cell Formatting

Instead of having VBA convert dates into text strings, write the raw date serials to the cells and apply a custom number format for a cleaner spreadsheet structure.

Advanced VBA Compatibility

Format Dates and Automate Tasks with WPS Spreadsheet

WPS Office Spreadsheet provides comprehensive support for macros and VBA, allowing you to seamlessly run your automated formatting scripts without compatibility issues.

1. Access the Developer Tools: Open WPS Spreadsheet, go to the 'Tools' tab, and click on 'Developer' to access the built-in VBA Editor.
2. Paste Your Formatting Macro: Insert a new Module and paste your corrected DateSerial and Format code directly into the workspace.
3. Run Automation: Execute the macro to instantly generate your 365 daily worksheets with perfectly formatted dates.

Fully compatible with Microsoft Excel VBA macros, scripts, and DateSerial functions.Accurately renders custom cell formatting like 'mmm dd yyyy (ddd)'.Lightweight and fast performance when generating and formatting hundreds of worksheets.Free to download and use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA macro return 1905 when using the Year function?

The Year() function expects a date serial number, not a 4-digit year. In VBA's date system, the number 2025 represents the 2,025th day after December 30, 1899, which evaluates to a date in the year 1905.

How do I format a date to include the day of the week in VBA?

You can use the Format function with the 'ddd' (short day) or 'dddd' (full day) arguments. For example, Format(MyDate, "mmm dd yyyy (ddd)") outputs a string like 'Jan 01 2025 (Wed)'.

Do I need to specify the first day of the week when formatting weekdays?

No, when you use the 'ddd' argument within the Format function, VBA automatically calculates the correct weekday based on the date serial without needing an explicit first-day-of-week parameter.

What is the difference between .Value and .Value2 in VBA when handling dates?

The .Value property can sometimes automatically convert data based on underlying cell formatting (e.g., forcing a Date or Currency type). The .Value2 property retrieves the underlying raw value (like the exact date serial number) without applying format conversions, making it faster and less prone to unexpected formatting errors.

Excel & Data Problems