Function Problems

How to Automatically Fill Dates and Weekdays in an Excel Calendar

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to create an automated calendar in a spreadsheet by automatically filling consecutive dates and displaying them as day numbers or weekday names via cell formatting.

Product: Excel
Device & OS: not provided
Scenario: Creating an automated monthly or yearly calendar layout without manual data entry for each day.
Observed behavior: Consecutive real date values are generated using formulas and visually formatted to show specific calendar components like days or weekdays.

Before you start

Ensure your spreadsheet program is set to recognize standard date formats and decide on the initial starting date for your calendar layout before applying formulas.

Solution 1Recommended

Auto-Fill Dates Using Addition Formulas and Custom Formatting

By leveraging simple addition formulas and custom number formats, you can quickly generate dynamic calendar dates that update automatically while preserving the actual date values.

Using actual date values and applying custom formatting is highly preferable to converting dates to text using the TEXT function. This method maintains the underlying numeric date value, which is essential if you plan to perform further date-related calculations, filtering, or conditional formatting later on.

Enter the starting date

Select the first cell of your calendar (e.g., A2), type your initial date in a standard format (like 01/01/2024), and press Enter.

Apply the addition formula

In the adjacent cell where you want the next consecutive day (e.g., B2 or A3 depending on your calendar layout), enter the formula '=A2+1'.

Drag to auto-fill

Select the cell containing the formula. Click and hold the fill handle (the small square at the bottom right corner of the cell), then drag it across the row or down the column to fill in the rest of the dates.

Format cells for day numbers or weekdays

Highlight all the filled date cells, right-click, and choose 'Format Cells'. Navigate to the 'Number' tab and select 'Custom'. To display just the day number, type 'd' in the Type box. To display the abbreviated weekday name (e.g., Mon, Tue), type 'ddd'.

Custom Format Variations: You can type 'dddd' instead of 'ddd' to display the full weekday name (e.g., Monday) or 'dd' to display a two-digit day number (e.g., 01, 02).

Efficient Calendar Creation

Easily Create Calendars with WPS Spreadsheet

WPS Spreadsheet offers robust date calculation formulas and an intuitive Format Cells interface, making it incredibly simple to build automated, professional-looking calendars without manual typing.

1. Open a new workbook: Launch WPS Spreadsheet and create a blank workbook.
2. Enter start date and formula: Type your starting date in the first cell, then use the '=cell+1' formula in the next cell and drag the fill handle to auto-populate the dates.
3. Apply custom format: Select the cells, right-click, choose 'Format Cells', go to the 'Custom' category, and apply your desired format such as 'd' or 'ddd'.

Fully compatible with Microsoft Excel date formulas and custom formatting codes.Intuitive Format Cells dialog for quickly applying 'd' or 'ddd' custom numbering.Lightweight software with rapid response times, even for large, multi-year calendar datasets.Free built-in calendar templates available for instant use.

microsoft office alternative - wps office

Frequently Asked Questions

How do I display the full weekday name instead of the abbreviation?

In the Format Cells dialog, under the Custom category, type 'dddd' in the Type box instead of 'ddd'. This will display the full name, such as 'Monday', instead of just 'Mon'.

Why do I see '#####' instead of my formatted dates in the calendar?

This happens when the cell column is too narrow to display the formatted date. You can easily fix this by double-clicking the right boundary of the column header to auto-fit the column width to your content.

Is it possible to auto-fill weekdays only and skip weekends automatically?

Yes. Enter your starting date, then right-click and hold the fill handle as you drag it down or across. When you release the mouse button, a context menu will appear—select 'Fill Weekdays' to automatically exclude Saturdays and Sundays.

Excel & Data Problems