Function Problems

How to Automatically Update Dates and Weekdays in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to automatically generate a sequence of consecutive dates and display their corresponding weekdays in an Excel table, ultimately to summarize daily quantities over time using a PivotTable.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a tracking spreadsheet where adding a starting date automatically populates subsequent days and extracts the day of the week to analyze data by weekday, month, and year.
Observed behavior: The user wants a clean, automated setup for the date column so that consecutive dates and weekdays are generated seamlessly, ready to be fed into a PivotTable for monthly and yearly comparison.

Before you start

Ensure your starting date is entered in a standard date format (e.g., MM/DD/YYYY) so that the spreadsheet software recognizes it as a date value and can properly execute addition formulas.

Solution 1Recommended

Use Auto-Fill Formulas and Date Formatting

The most efficient way to generate consecutive dates and weekdays is by using a simple addition formula and adjusting custom number formatting.

Because dates are stored as serial numbers in spreadsheets, adding 1 to a date cell automatically calculates the exact next day.

Enter the starting date

Select the first cell of your column (e.g., cell A3) and type your initial date.

Apply the addition formula

In the cell directly below it (A4), enter the formula =A3+1 and press Enter.

Fill the formula down

Click the small square fill handle at the bottom-right corner of cell A4 and drag it down to automatically generate consecutive dates for the rest of your table.

Format to show the weekday

To display the day of the week in the same cell, select your date cells, right-click, and choose 'Format Cells'. Go to the 'Custom' category and type 'dddd, mmmm d, yyyy' (or just 'dddd' to show only the day name).

Solution 2

Extract Weekdays into a Separate Column using the TEXT Function

If you need the weekday isolated in a completely separate column for easier grouping or filtering, use the TEXT function.

Solution 3

Summarize Daily Quantities Using a PivotTable

Once your dates and weekdays are set up, insert a PivotTable to summarize quantities by weekday, month, or year.

Advanced Spreadsheets

Manage Dates and Summarize Data Easily in WPS Spreadsheet

WPS Spreadsheet offers full compatibility with Excel formulas, date formatting tools, and PivotTables. You can effortlessly automate dates, extract weekdays, and build complex PivotTable summaries to analyze your data over time.

1. Set up your dates: Open your document in WPS Spreadsheet, type your starting date, and use the =cell+1 formula in the next row to auto-generate the sequence.
2. Extract the weekday: Use the =TEXT(cell,"dddd") formula in an adjacent column to automatically output the day of the week.
3. Analyze with PivotTables: Select your data table, go to the 'Insert' tab, click 'PivotTable', and drag your fields to summarize daily or monthly quantities.

Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas.Supports advanced date functions including TEXT, WORKDAY, and arithmetic operations.Powerful PivotTable features for grouping and summarizing data by day, month, and year.Free to use with a lightweight, intuitive tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my date formula showing as a random 5-digit number?

Dates in spreadsheet applications are stored as sequential serial numbers. If you see a 5-digit number (like 44200), simply select the cell, go to the Home tab, and change the Number Format dropdown from 'General' to 'Short Date' or 'Long Date'.

How do I make the formula skip weekends and only show workdays?

Instead of adding 1, use the WORKDAY function. For example, typing =WORKDAY(A3, 1) will automatically return the next consecutive weekday, successfully skipping Saturdays and Sundays.

Can I automatically group individual dates by month in a PivotTable?

Yes. Once you create your PivotTable and drag the date field into the Rows area, right-click any date in the PivotTable and select 'Group'. Choose 'Months' and 'Years' to automatically summarize your daily data into higher-level timeframes.

Excel & Data Problems