How to list all days as date in a specified month in Excel?
When working with date-based data in Excel, creating a list of every single day in a particular month is a frequent need—for example, for planning, scheduling, reporting, or tracking purposes. While Excel’s Fill Series function is commonly used for this task, ensuring your list doesn't run past the last day of the month can be a challenge, especially for months with varying numbers of days. If you need a method to produce an accurate, automatic list matching the precise number of days in any specified month, this article provides several practical solutions tailored for different Excel skill levels and scenarios.
List all days as date in a specified month with formula
VBA Code - Automatically generate all dates of a month
Built-in Excel Method - Fill Series Feature
List all days as date in a specified month with formula
Formula is a flexible, cell-based solution for anyone needing to list all the dates within a single month. This method allows you to generate and fill dates automatically, with no need to manually count the days. It's ideal for simple, repeatable tasks where you might be preparing multiple sheets for different months. Please follow these steps:
1. Begin by typing the first day of the desired month into a blank cell. For example, enter 2016/7/1 into cell A2. You can use your target month and year as needed.
2. Click into the cell directly below your starting date (here, it's cell A3). Enter this formula and press Enter:
=IF(A2="","",IF(MONTH(A2+1)<>MONTH(A2),"",A2+1))
This formula checks whether to increment the date and will leave the cell blank once the dates move out of the month you started with. Even as you drag it farther than the month's end, it will not show extra dates.
3. Next, ensure your result cell displays in date format. Find the cell you just entered the formula in, then navigate to Home > Number Format drop-down > Short Date. This step is important—if the cell is in General or Number format, you may only see a serial number rather than an actual date.
4. With the formula cell still selected, drag the Fill Handle (the small square at the cell’s bottom-right) downward. Continue dragging beyond the expected number of days in your month—for example, if the month is July, you could safely drag at least31 rows. No matter how far you drag, dates will only populate cells belonging to the specified month, preventing spillover into the next month.
VBA Code - Automatically generate all dates of a specified month
For users who want to avoid manual formula entry, especially when generating date lists repeatedly or across multiple sheets, a simple VBA macro can automate this process. This approach is especially useful when you want to generate a customizable date series in just a few clicks. With VBA, you can prompt for the target month and year, and the code will generate all relevant dates for you quickly.
1. Click Developer Tools > Visual Basic to open the Microsoft Visual Basic for Applications editor. In the VBA editor, click Insert > Module and paste the following code into the newly created module:
Sub ListAllDatesOfMonth()
Dim y As Integer
Dim m As Integer
Dim startDate As Date
Dim endDate As Date
Dim i As Integer
Dim xTitleId As String
Dim destCell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
y = Application.InputBox(prompt:="Please enter the year (e.g.2023)", Title:=xTitleId, Type:=1)
If y = 0 Then Exit Sub
m = Application.InputBox(prompt:="Please enter the month number (1-12)", Title:=xTitleId, Type:=1)
If m = 0 Then Exit Sub
Set destCell = Application.InputBox(prompt:="Select the starting cell for your dates", Title:=xTitleId, Type:=8)
If destCell Is Nothing Then Exit Sub
startDate = DateSerial(y, m, 1)
endDate = DateSerial(y, m + 1, 0)
For i = 0 To endDate - startDate
destCell.Offset(i, 0).Value = startDate + i
Next i
destCell.Resize(endDate - startDate + 1, 1).NumberFormat = "yyyy/m/d"
End Sub2. Click the 
Run button (or press F5), and respond to the prompts to enter the year, the month, and to select the cell where the dates should begin.
Notes and Tips:
- For month input, type the number—January is1, February is2, etc.
- All generated dates will be formatted as "yyyy/m/d". You can edit the date format in the NumberFormat line if desired.
- If you make an input error or want to redo the list, simply rerun the macro.
- This solution is best when you routinely create date lists or automate repetitive reporting.
Built-in Excel Method - Fill Series Feature
Another straightforward built-in Excel method for listing all days in a month is using the Fill Series function—this approach is especially helpful for users unfamiliar with formulas or VBA. By explicitly defining the step value (1 day) and specifying the stop value as the last day of your target month, you can create an exact date list for any month.
- Enter the first date of the specific month into a blank cell (for example, A2, type 2023/4/1 for April 1,2023). Make sure Excel recognizes your entry as a date—if unsure, you can use =DATE(2023,4,1) in the cell for clarity.
- Select the starting cell (A2). Then, go to Home > Fill > Series... to open the Series dialog box.
- In the Series dialog:
- Choose Columns if you want the dates listed down the column; choose Rows for a horizontal list.
- Set the Type to Date.
- Set Date unit to Day.
- Set the Step value to 1 (to increment by one day).
- In Stop value, type the last day of the target month. For example, if April 2023, enter 2023/4/30.
- Click OK. Excel will now fill the selected cell range with all dates from the first to the last day of the specified month.
Advantages and Notes:
- This method does not require formula memorization or editing.
- You need to manually supply both the start and stop dates for each month.
- If you make a mistake in the stop value, the list will either be incomplete or will include extra days. Double-check your end date, especially for February and leap years.
- After filling the series, you can format all cells as dates using Home > Number Format if needed.
Each solution above offers unique benefits: formulas are dynamic and easy to copy to other months; VBA enables one-click automation for large-scale use; Fill Series is fast and visual for occasional tasks. Should you encounter issues, confirm your entered dates are in a recognizable format, and when using VBA, be sure macros are enabled and valid cell ranges are selected. For more complex scheduling (including weekends, holidays, or specific workdays), additional formula logic or VBA adaptations may be needed.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in