How to set a date to the first day of the month in Excel?
When working with datasets in Excel, there are often situations where you need to normalize dates—for example, converting any given date to the first day of its month. This can be very useful for financial analyses, monthly reports, or for grouping data by month regardless of the day, as shown in the example here. This guide introduces two practical Excel formulas and a VBA code you can use to easily convert any date to the first day of its corresponding month.
Set or convert date to first day of given month with formulas
Set or convert date to first day of given month with formulas
To quickly change a list of random dates to the first day of each of their months, Excel provides a couple of straightforward formulas. This approach is highly effective when you need to standardize dates for pivot tables, monthly aggregations, or periodic data reviews.
1. Click on a blank cell next to your date (for example, use cell B2 if your original date is in A2). Enter the following formula:
=EOMONTH(A2,-1)+12. After entering the formula, press Enter. The cell will display the first day of the month for the date in A2.
3. To apply this formula to the rest of your dates, move your mouse to the lower-right corner of the cell until you see the fill handle (+ symbol). Click and drag the fill handle down through the range of cells you want to convert. Each date will automatically convert to the first day of its month. See screenshot:
Troubleshooting and Tips:
- If the formula result displays as a serial number, you may need to format the cell as a date. Right-click the cell, select Format Cells, and choose a date format.
- Make sure your date values in column A are valid Excel date entries, otherwise the formulas may return errors.
Notes:
1. There is an alternative formula you can use, which may be preferred if you want a simpler structure or if EOMONTH is not available in your Excel version. In a blank cell, enter:
=DATE(YEAR(A2),MONTH(A2),1)This formula directly extracts the year and month from your date and sets the day as 1, thus giving you the first day of the month. As before, you can drag the formula down to fill other cells in the column.
2. In both formulas, A2 should be replaced with the cell reference of the date you want to convert. Adjust the cell reference if your data is in a different column.
Usability and Limitations:
- These methods are suitable for standard Excel worksheets. If you are working with large datasets or need to automate this for multiple files, consider using VBA or additional batch tools for efficiency.
- Formulas work best on clearly defined date columns—mixed data types or text strings that look like dates may require pre-cleaning.
Set or convert date to first day of given month with VBA
For users who often need to perform this operation in different workbooks or for very large numbers of dates, using a VBA macro can save time. This method is also useful if you want to convert multiple columns at once or automate the process. Here's how to do it:
1. Go to Developer Tools > Visual Basic to open the VBA editor. In the editor window, click Insert > Module, and then paste the following code:
Sub DatesToFirstOfMonth()
Dim rng As Range
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range for conversion", xTitleId, rng.Address, Type:=8)
For Each cell In rng
If IsDate(cell.Value) Then
cell.Value = DateSerial(Year(cell.Value), Month(cell.Value), 1)
End If
Next cell
End Sub2. After entering the code, click the 
(Run) button or press F5 to execute the macro. You will be prompted to select a range—pick the range with your date values, and all selected dates will be changed to the first of their respective months.
- If you receive a "Type Mismatch" error, check to ensure the selected cells contain valid date values.
- Remember to save your work before running VBA code, as macros can't be undone with Ctrl+Z.
With these options, you can efficiently convert any list of dates to the first day of their month, whether you prefer using formulas or automating the task with VBA. Choose the method that matches your workflow and data size best. If you encounter errors or unexpected results, double-check cell formats and cell references, and consider using Excel's Text to Columns tool to clean ambiguous date values beforehand.
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