Do you need to get the number of days in a month in Excel?

Getting the number of days in a month is required to efficiently manage things like payroll, forecasting, and project planning in Excel.

You might already use Excel for data analysis, project management, and financial calculations. Then, you must understand how to manipulate date-related functions to enhance your efficiency and productivity when working with Excel.

One common task is determining the number of days in a given month. It’s crucial for various calculations, such as workforce requirement forecasting, project time tracking, or project scheduling.

Whether you’re a seasoned Excel user or just starting your journey, this guide will walk you through the steps to effortlessly obtain the exact number of days in any month using proven methods.

Reasons to Populate Number of Days in a Month in Excel

You might say that you remember the sequence to recall how many days in a given month. However, relying on your or someone else’s memory isn’t a professional way of working in Excel.

Here, you must create formulas or VBA codes for every task you do so that you can easily track down the inputs and outputs. Therefore, find below the benefits of getting numbers of days as an integer in Excel instead of entering this manually:

1. Knowing the number of days in a month ensures precise calculations in Excel
2. Enables accurate measurement of time intervals for tasks, billing cycles, and project timelines
3. Essential for financial projections, budgeting, and resource allocation
4. Allows for dynamic and automated monthly summaries, deadline tracking, and invoicing
5. Saves time and effort by using Excel’s functions instead of manual counting
6. Works as a valuable Excel skill during interviews as an office assistant, data analyst, data scientist, etc.

Now that you know why you must learn how to populate the total number of days from a month in Excel, let’s explore some quick methods below:

Fetch Total Days In a Month Using the EOMONTH Function

EOMONTH function is the most widely used method to obtain the number of days in a month in Excel. This formula calculates the last date of a required month. In this method, you get the last day of the month for a specific month and its previous month and subtract the two values to get the total number of days in the given month. Find below the functional steps you can follow:

1. Select a cell adjacent to a reference day of the month for which you need to get the total days.
2. Copy the following formula and paste it into the selected cell:

=EOMONTH(B2,0)-EOMONTH(B2,-1)

3. In the above formula, B2 is the reference date. Adjust the cell reference B2 to something else according to your dataset.
4. Hit the Enter key to get the total number of days for the month in the reference date.

5. Now, drag the fill handle down the column to obtain the total number of days for the rest of the months in your dataset.

Get the Sum of Days in a Month in Excel Using the DAY and EOMONTH Functions

Another easy formula that you can use to obtain the total number of days in a given month is a combination of the DAY and EOMONTH functions. You’ll however need a reference column of dates of different months. Here’s the formula and steps to use it in any real-world scenario:

1. Let’s consider, you’re making a payslip for an employee and there are six payment dates under column B on your Excel worksheet.
2. Under column C, in cells C2 through C7, you need to populate the total days for which you’ve paid the employee. The total paid days are equivalent to the total days in the corresponding months.
3. Select cell C2 and enter the following custom formula in it:

=DAY(EOMONTH(B2,0))

4. Hit the Enter key to get the total days in a month. In this tutorial, it’s 31 for the month of May.

5. Select this value and drag the fill handle down the column to populate the total number of days for the rest of the months.

Find Out the Total Days in the Current Month Using the DAY Function

Consider, your Excel workflow requires the automated code to pick up the current month from system settings and accordingly calculate the total days in that month. Here’s is the formula that you need to use to accomplish this task:

1. Select any cell on your Excel worksheet.
2. Enter the following formula inside the selected cell:

=DAY(EOMONTH(TODAY(),0))

3. Hit the Enter key and you instantly get the sum of days in the current month.

Populate Total Days From the Month Name

Suppose, you’ve got the month names only and you need to populate the corresponding total days in each month. You can probably do it manually if you were to do this only once in your career. But, what if you need to do this every day or you’d like to do it using a formula? Here’s how you can accomplish this:

1. Select the cell where you need to populate the days in the month of January. Preferably, the cell should be adjacent to the cell that contains the text January in your list of months.
2. Copy and paste the following formula into the selected cell and hit the Enter key:

=DAY(EOMONTH(DATEVALUE("01-"&A2&"-"&YEAR(TODAY())),0))

3. You should see 31 in the above cell.

4. Now, drag the fill handle down the column until the adjacent cell of the month of December.
5. Excel will automatically populate the total number of days for the rest of the months.

You can effortlessly use this formula with other custom formulas to automate the task in your Excel worksheet and never have to manually type the number of days of months in Excel.

Know the Number of Days in a Given Month Using a VBA Script

Using this VBA script is one of the most effortless ways to fetch the number of days in a month. Once you hit the Run button for this macro, you need to enter the year and month to get the number of days. This script is the perfect program that you can add as an Excel Quick Access button. Anyone working on the Excel file can hit the button to learn the total days in a month. Here’s how to implement the VBA script in your worksheet:

1. Open the Excel worksheet where you need to frequently check the sum of days in a month.
2. Hit the Alt + F11 keys together to bring up the Excel VBA Editor utility.
3. Now, click the Insert button on the VBA Editor toolbar.
4. Choose Module in the drop-down list that pops up.
5. Inside the new blank Module on VBA Editor backstage, copy and paste the following VBA code:

Sub GetDaysInMonth()
    Dim year As Integer
    Dim month As Integer
    Dim lastDay As Integer
    
    ' Set the year and month values
    year = InputBox("Enter the year:")
    month = InputBox("Enter the month (1-12):")
    
    ' Check if the entered month is valid
    If month < 1 Or month > 12 Then
        MsgBox "Invalid month value. Please enter a value between 1 and 12.", vbExclamation
        Exit Sub
    End If
    
    ' Get the last day of the month
    lastDay = Day(DateSerial(year, month + 1, 1) - 1)
    
    ' Display the result
    MsgBox "The number of days in the month is: " & lastDay, vbInformation
End Sub

6. Click the Save button and hit Yes on the macro-enabled Excel file dialog box.
7. Now, close the VBA Editor to get back to your Excel worksheet.

8. Press Alt + F8 to bring up the Macro manager dialog box.
9. There, select the GetDaysInMonth macro and click the Run button.

10. In the dialog box that follows, enter the year.

11. In the next pop-up, enter a month from January to December where January is 1 and December is 12.

12. Hit the OK button and a dialog box will show the number of days in the selected month.

Get the Number of Days in a Month Using Office Scripts

If you’ve got the Automate tab on your Excel desktop and web app, you can automate the formula-based process by using the following Office Scripts code:

1. On your worksheet, create a column named Number of Days on the right side of the column containing short dates. Let’s consider the new column as column C.
2. Now, go to Automate tab and select New Script.
3. On the Code Editor, copy and paste the following script:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setFormulaLocal("=EOMONTH(B2,0)-EOMONTH(B2,-1)");
	// Auto fill range
	selectedSheet.getRange("C2").autoFill("C2:C6", ExcelScript.AutoFillType.fillDefault);
}

4. Before you can use the above script, make some changes as suggested below:
   • Change all references of C2 to the cell reference where you want to get the number of days. If it’s under column F, then type F2.
   • Change C2:C6 to a cell reference where you want Excel to populate the number of days in a month against the given dates. For instance, if you’re populating the same under column F for hundred rows, it should be F2:F101.
   • Change all the references of B2 to an appropriate cell reference. For example, if all the date values are under column E, then they should be E2 in place of B2.
5. Click Save script and then hit the Run button to fetch the total days in a month. You get the data under column C or the cell range you selected in the code.

Get the Number of Days in a Month Using Power Querry

If you’re importing large datasets from a database where you also need to get the number of days in a month, you can use the Power Query tool of Excel. Here’s how it works:

1. Open the worksheet and click the Data tab on the Excel ribbon.
2. On the Get & Transform Data block, click Get Data and import the target data.

3. If the dataset is already on the worksheet, select the entire dataset and click From Table/Range.

4. Inside the Power Query Editor, click Add Column and choose Custom Column.
5. On the Custom Column wizard, set a name for the new column like Number of Days, and enter the following formula:

Date.DaysInMonth([Payment Dates])

6. In the above formula, change the [Payment Dates] value to the actual column header under which short dates are available in rows.
7. Click OK to apply the Custom Column rules.

8. Click Files and choose Close & Load to enter the Power Querry data into the Excel workbook on a new sheet.

Conclusions

You’ve explored several different methods to enumerate the total number of days in a month in Excel.

This skill will help you automate various tasks you do as a project manager, workforce manager, or payroll manager.

Choose a method from the above depending on the scenario and your Excel dataset.

Don’t forget to comment below to let me know how was your experience with the above Excel tips and tricks.