OmniDocHow to fill in the date from 1 to 30 in Excel and display the day of the week
Explain how to automatically fill in dates from 1 to 30 in an Excel table and display the day of the week next to each date, including drag-and-drop filling, date formulas and custom formats, with an itemized checklist attached.
Key points of this article
- For consecutive dates, you can first enter the starting date, and then drag the fill handle in the lower right corner of the cell to expand downward. Excel defaults to incrementing by day.
- When you only need serial numbers from 1 to 30, enter the first two numbers and drag them together, and Excel will continue in the same step.
- The date should be displayed as "the day of the month", which depends on the cell format setting. The value of the date itself remains unchanged, which facilitates subsequent calculations.
- The day of the week can be displayed directly in a custom format, or generated using the TEXT or WEEKDAY function, with different result forms.
- After filling, check whether it is a true date, whether it spans months, and confirm that the display language of the week is consistent with the system.
If you want to make a duty schedule, time clock, or monthly progress schedule, you often need a column from No. 1 to No. 30, and each row must be marked with the corresponding day of the week. Manually entering one by one is slow and error-prone. After changing the month, the week has to be entered again. The idea of automatically filling in the dates from Table 1-30 is actually very simple: first generate the real date, and then let the format or function "paste" the day of the week.
This article is written for readers who often make forms but don’t want to have to figure it out from scratch every time. You will learn how to use drag and drop, formula and custom format to generate consecutive dates and display the days of the week accordingly. You will also learn what scenarios each method is suitable for and how to check for problems.
How to fill in the dates from 1 to 30 so that the day of the week can be displayed at the same time? One sentence conclusion
First use the starting date and drag and drop to fill in to generate consecutive dates, then use cell format to display the date as "the day of the month", and use a custom format or function to add the day of the week. In most cases, these three steps are enough.
| your situation | Recommended practices | reason |
|---|---|---|
| Just need a column of consecutive dates, don't care about the day of the week | After entering the start date, drag the fill handle | Fast operation, Excel increments by day by default |
| Requires display style like "March 1st" | Set the cell to the custom format "m month d day" | The cells still contain date values for easy calculation. |
| I want the date to be followed directly by the day of the week | Set a custom format for the date column "m month d day aaaa" | No extra columns are added, the table is cleaner |
| It is necessary to save the week in a separate column for filtering or statistics | Generate new columns using the TEXT or WEEKDAY function | The results are independent data that can be filtered and counted |
| Just a serial number from 1 to 30, not a date | Enter 1 and 2 in the first two spaces and drag them together | The sequence continues in fixed steps and does not involve days of the week |
Before you start: Confirm these points first
- Confirm that the start date is a real date, not a text. The text "2025.3.1" looks the same as the date, but it cannot participate in increment and cannot calculate the week. The judgment method is very simple: select the cell and check the alignment. Numeric dates are placed on the right by default, and text is usually placed on the left.
- Confirm the number of days and months to be filled in. 30 days from the 1st to the 30th. If you want the entire month, February only has 28 or 29 days. For 31-day months, add one extra day. Don't just apply 30.
- Confirm whether you want a date or a serial number. As long as numbers from 1 to 30 are used, serial numbers are sufficient; to display the day of the week, it must be a real date. There is no day of the week in a pure serial number.
- Confirm the locale of Excel. In the Chinese environment, the format code "aaaa" will display "Monday"; in the English environment, "dddd" should be used, which will display Monday. Before writing formulas or setting formats, confirm the version language.
- Confirm whether there is already content in the form. If the target area already contains data, formulas or merged cells, dragging directly may overwrite the original content. It is recommended to try it out in a blank column first and make sure it is correct before applying it to the official form.
Steps to fill in dates from 1 to 30 and display the day of the week in Excel
- Enter the starting date in cell A1, for example, 2025/3/1, and press Enter to confirm.
- Click A1, move the mouse to the lower right corner of the cell, and when the cursor changes to a black cross, this small square is the fill handle.
- Hold down the fill handle and drag it down to A30, then release the mouse. Column A will appear from March 1st to March 30th, which are the true dates in daily increments.
- Select column A, open the cell format settings, enter "m month d day" in the custom format, the date will be displayed as "March 1", and the numerical value will not change.
- If you want the day of the week and the date to be displayed in the same cell, change the custom format to "m month d day aaaa", and the cell will display "March 1 Saturday" without the need for additional columns.
- If you want to put the week in a separate column, enter the formula =TEXT(A1,"aaaa") in B1, press Enter and drag down to B30. The corresponding week will be displayed in each row.
- If you need the day of the week in numeric form, enter =WEEKDAY(A1,2) in C1. Monday will return 1 and Sunday will return 7, which is convenient for shift scheduling or statistics.
- If you want the entire column to automatically update after modifying the start date, change A2 to the formula =A1+1, and then drag down to A30. As long as A1 is modified later, all dates below will change accordingly.
Check each item after completion
After you get the results, don't hand them over directly. Take a minute to check them one by one according to the table below.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Are the dates consecutive? | Increment from line 1 to line 30, no skips or duplications | Check whether the fill handle is dragged to the correct row or whether the formula reference is misplaced |
| Whether it spans months | When the starting date is close to the end of the month, the date of the next month will appear later. | Confirm the range according to actual needs. If you only need the current month, reduce the number of rows to the number of days in the current month. |
| Is the day of the week correct? | Check the calendar for two or three days. For example, the weekend should be Saturday or Sunday. | Check if the format code matches the locale or if the start date is text |
| Is the date a true date? | Select the cell to see the alignment. By default, the digital date is on the right. | Re-enter, or use functions such as column sorting to convert the text into dates. |
| Whether the original content is overwritten | After dragging, check whether the formula or data next to it is still complete | Redo in blank columns after undoing to avoid overwriting formal data |
❓ Frequently Asked Questions (FAQ)
1. The date is not incremented after dragging, but the same date is copied. What should I do?
In most cases, the filling method is selected as "Copy Cells". After releasing the mouse, click the fill option button that appears next to it and change it to fill by day. Also, this happens if the starting cell contains text instead of a date.
2. How to fill in the numbers from 1 to 30 directly as serial numbers instead of dates?
Enter 1 in A1 and 2 in A2, select both cells, and then drag the fill handle to A30. Excel will recognize that the step size is 1 and fill in the numbers from 1 to 30.
3. Why does the day of the week show Monday in English?
This is caused by the locale. In the English version of Excel, the format code should be changed to "dddd". In the Chinese environment, using "aaaa" will display "Monday". If the two environments are mixed, it is recommended to unify the system or Excel language settings to avoid inconsistent table display on different computers.
4. There are 31 days in the whole month, what should I do?
Simply extend the filling range to 31 lines. If you want the table to adapt to different months, you can use the formula =A1+1 to extend row by row and determine the range based on the number of rows needed. In months with fewer days, such as February, the extra rows will automatically go to the next month. Just delete the extra rows.
📝 Summary
The core of automatic filling of dates in tables 1-30 is to first generate the real date, and then use a format or function to display the day of the week. Drag-and-drop filling is suitable for quick generation, formulas are suitable for linking with the start date, and custom formats are suitable for keeping the table clean. After finishing, check it according to the checklist, and you can use it with confidence.
