7 Ways to Get the Number of Days in a Month in Microsoft Excel

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:

Using the EOMONTH formula
Using the EOMONTH formula
  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)
  1. In the above formula, B2 is the reference date. Adjust the cell reference B2 to something else according to your dataset.
  2. Hit the Enter key to get the total number of days for the month in the reference date.
Obtain total number of days in a month in Excel
Obtain the total number of days in a month in Excel
  1. 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:

Get the number of days in a month using DAY and EOMONTH
Get the number of days in a month using DAY and EOMONTH
  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))
  1. Hit the Enter key to get the total days in a month. In this tutorial, it’s 31 for the month of May.
Use fill handle to copy and paste formula
Use the fill handle to copy and paste the formula
  1. 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:

Current month's days in Excel
Current month’s total days in Excel
  1. Select any cell on your Excel worksheet.
  2. Enter the following formula inside the selected cell:
=DAY(EOMONTH(TODAY(),0))
  1. 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:

Calculating days of months
Calculating days of months
  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))
  1. You should see 31 in the above cell.
Dragging fill handle
Dragging fill handle
  1. Now, drag the fill handle down the column until the adjacent cell of the month of December.
  2. 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:

Creating a VBA script in Excel
Creating a VBA script in Excel
  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
  1. Click the Save button and hit Yes on the macro-enabled Excel file dialog box.
  2. Now, close the VBA Editor to get back to your Excel worksheet.
Running Excel VBA macro
Running Excel VBA macro
  1. Press Alt + F8 to bring up the Macro manager dialog box.
  2. There, select the GetDaysInMonth macro and click the Run button.
Enter year
Enter year
  1. In the dialog box that follows, enter the year.
Enter month
Enter month
  1. In the next pop-up, enter a month from January to December where January is 1 and December is 12.
Days in January
Days in January
  1. 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:

office script for number of days in a month
  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);
}
  1. 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.
  2. 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:

Get Data Power Query
  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.
From table or range
  1. If the dataset is already on the worksheet, select the entire dataset and click From Table/Range.
Custom column
  1. Inside the Power Query Editor, click Add Column and choose Custom Column.
  2. 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])
  1. In the above formula, change the [Payment Dates] value to the actual column header under which short dates are available in rows.
  2. Click OK to apply the Custom Column rules.
Close and load
  1. Click Files and choose Close & Load to enter the Power Querry data into the Excel workbook on a new sheet.
number of dasy from power query

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

1 Comment

  1. Jay Case

    Thanks for this lesson. I have been going nuts how to automate my yearly calendar and I see how I can use what you show to Do about 75%.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃