How do I get monthly data in Excel?

Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply the number format of your choice.

It's a rare occasion when you'll find yourself in a situation where you need to get the month name from a date in Excel. Most of the time, people go on Google to find this kind of information  Month names is important in Excel. Knowing how to get the month name from the date in your worksheet is necessary because there are many tools that need the month name.

In this post, we will discuss how to get the month name from the date in Excel. Year, month, and day can be displayed on Microsoft Excel in the sheet tabs. You can also set the date format for each record by selecting appropriate date information through the drop-down list. To get these details and other information about a particular month as shown in cells, follow these steps:

Tips that are provided in this article are compatible with versions 2010/2013/2016.

How to get month name in excel from a date in Excel: Cell formatting Method

If you are someone who don’t like putting up long functions in the formula  bar and wait for all those errors that you can’t mix then this method is for you , all you need is to change the format of your date and boom that‘’s it your job is done

1.Select the date , left click on that cell.

2.And select the format cell.

3.Select the custom option.

4.And type mmmm

5.This will convert the date into the respective month.

How to get month name from a date in Excel: Using Month Function

If you find the above method a little hard for you then you use a simple formula for conversion of date into the month. The month formula in Excel is used to calculate the number of months between two dates.

You can use this formula to calculate how many months there are between two dates by simply entering the first date in cell A1, and then entering the second date in cell B1. The formula will return a single number, which represents the number of months between these two dates. The formula is

MONTH(CELL NO)

The monthly formula in Excel is a very useful tool. It can help you calculate a variety of financial data and to do that accurately and quickly It is quite useful when you want to change a number of your dates into the month format for further calculations It returns the  number of month instead of that name (Jan(1) to December (12))

1.Select the cell with date inside it

2.Use the formula.

3.This will convert your date into the respective month like we can see ,here it’s converted to the 9th month

 

How to get month name from a date in excel : Using Text function:

Text function is another way to convert your date into the month ,the syntax of this function is

Text(Cell Number, ’ldquo;mmm‘;)

1.Here’s how to use this function to get month name, select the cell and write this formula.

2.You can see we have got our required month in the respective selected cell

Did you learn about how to get month name from a date in excel? You can follow WPS academy to learn more features of Word documents, Excel Spreadsheets, and PowerPoint slides.

You can also download WPS office to edit the word documents, Excel Spreadsheets, and PowerPoint free of cost. Download Now! And get an enjoyable working space.

This tutorial will teach you how to convert weekly summary data into monthly total data by allocating the days in each week to the appropriate month of the year. Let’s dive in!

Examine the Data Set

First off, let’s take a look at our sample data…

Here we have some pretty standard weekly aggregated sales data. In Column A, we have a date field listing the ending date of each week (Saturday). In Column B we have total sales that occurred in that week.

Throughout this tutorial, we are going to assume that the date provided in your weekly data is the week end. It is still possible to convert week beginning dates and data, but the formulas will need to be changed.

Conversion Assumptions

Since we have only summarized data about each week, there is no way to know exactly what each day’s data was, so you will need to make some assumptions about the daily data. In this tutorial, we’ll assume that each day is average – 1/7th of the week’s total. You could make a different assumption, but it would make the formulas more complicated.

To allocate the correct number of days to each month, we need to build a formula that counts how many days in the week were in the prior month and how many were in the current month. Most weeks will be fully in the current month, so that month will get all of the days. To do this clearly, we are going to create a helper column that helps us calculate the conversion.

Making the Helper Column

In the column next to your weekly data (in our case Column C), create new header and call it “Days In Month” or something similar (the title is just for reference). In the row with the first line of data, type the following formula and press ENTER:

=MIN(A2-EOMONTH(A2,-1),7)

This formula uses the EOMONTH() function. This function provides the date value for the last day of any month, relative to the date provided (The End Of the MONTH). The syntax for the EOMONTH() function is as follows:

EOMONTH(start_date, months)

The start_date is the reference date in Excel date format. The months input is any integer number, positive or negative. If months is zero (0), EOMONTH() provides the last date of the start_date month. If 1, the last day of the next month. If -1, the last day of the prior month, and so on.

In this example, EOMONTH(A2,-1) returns the last day of the month prior to the date in Column A.

The MIN() function just returns the lower of two numbers. In this example, it will calculate (A2 – EOMONTH(A2,-1)) and return whatever is lower – the answer, or the number 7.

In cell C2, The formula is calculating as follows:

MIN(7-Jan-17 - 31-Dec-16 = 7, 7)

In this case, the answers are the same, so the MIN() function returns 7.

Drag the formula down to copy it to all the rows in your data set. When you are finished, you should have a table of data like this:

Now, we can add up all the parts of the month to get our monthly totals…

Building the Monthly Total Formula, Part 1

To begin, we need to set up a monthly table.  Next to your data table, build a row of month calendar headings using dates, like shown:

Now, we can begin to capture the portion of the data that falls into each month. To begin, type the following formula in cell E2 and press ENTER:

=SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1),1))*$C$2:$C$54/7*($B$2:$B$54))

It will look like below, and don’t worry – we’ll walk through what’s going on:

The SUMPRODUCT() function is one big multiplication calculation, but it doing that multiplication for every row in the data table. That’s what the ranges are for ($A$2:$A$54, $B$2:$B$54, and $C$2:$C$54). The part with the DATE() functions is actually comparing two dates and returning a 1 if they are equal and a 0 if they are inequal. Let’s look closer:

(DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1) = DATE(YEAR(E$1),
MONTH(E$1),1))

The first DATE() function is building an Excel date value equal to the month and year of every row in the data table. The second DATE() function is building an Excel date value equal to the month and year of the monthly calendar table.

If they are equal, the SUMPRODUCT() will include the row in the sum. Otherwise, the formula will add zero (excluding them from the sum).

For every row with a date in the current month/year, the rest of the formula is considered:

$C$2:$C$54/7*($B$2:$B$54)

Since Column C is our helper column, it contains the number of days that count in the current month. That number, divided by 7, gives us the fraction of the weekly total to allocate to the current month’s total. We multiply that by the total in Column B, and that’s it!

The SUMPRODUCT() takes the sum of all rows with partial weeks in the current month and adds them together to get the first part of our monthly total.

Building the Monthly Total Formula, Part 2

The first SUMPRODUCT() formula got most of the values we needed, but it left out the partial months that ended mid-week. For example, the week ending February 4, 2017 has 3 days of January in it! To add these days to the total, we need to build another SUMPRODUCT() formula…

Type the following formula in cell E3 and press ENTER:

=SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1)+1,1))*(7-$C$2:$C$54)/7*($B$2:$B$54))

It will look like below:

The same logic is at work here as in the previous formula with a few minor tweaks. Instead of comparing dates in Column A to the current month in the monthly calendar table, the formula compares it to the next month in the calendar table:

(DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1)+1,1))

The only difference is a small +1 added to the MONTH() portion of the DATE() formula, highlighted here in red.

The rest of the SUMPRODUCT() formula has also changed:

(7-$C$2:$C$54)/7*($B$2:$B$54)

Instead of using helper Column C as the fraction, we are taking the opposite fraction. Since 7 is our denominator, we start with 7 and subtract the number of days in the (next) month to get the remainder. That fraction of the week is multiplied by the weekly total data and then added to our SUMPRODUCT() total.

Combining the Monthly Total Formulas

Fortunately, these two SUMPRODUCT() functions can be added together in a single cell to create a total monthly figure for each of the monthly calendar columns. It is as simple as pasting the formulas together with a + in the middle:

=SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1),1))*$C$2:$C$54/7*($B$2:$B$54)) +
SUMPRODUCT((DATE(YEAR($A$2:$A$54),MONTH($A$2:$A$54),1)=DATE(YEAR(E$1),
MONTH(E$1)+1,1))*(7-$C$2:$C$54)/7*($B$2:$B$54))

It will look like below when completed:

You can drag all three formulas to the right to copy them to each monthly calendar table column. At the end, you should have the same total amount across all your months as you have in your original weekly data set. You can delete the first two rows of the calendar if you prefer, leaving only the combined total monthly sums.

Download the Sample File

The example data used here, as well as all of the formulas discussed is available for download below. You can experiment with the example data in the embed, or download the entire Excel spreadsheet by clicking the download in the bottom right.

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!