How to Calculate MTD (Month to Date) in Excel (3 Methods)

In this tutorial, we will demonstrate how to calculate MTD (Month to Date) in Excel. Microsoft Excel offers a variety of options for calculating MTD depending on several factors. After this article, you will be able to calculate MTD easily with different methods.

Download Practice Workbook

We can download the practice workbook from here.

What is MTD?

The term MTD refers to ‘Month to Date.’ It’s the time span from the start of the present month to the current time but not today’s date, as it may not yet be finished. MTD is used to provide information on a specific activity for a specific time period.

3 Easy Methods to Calculate MTD (Month to Date) in Excel

1. Combine SUM, OFFSET, ROWS & DAY Functions to Calculate MTD in Excel

Suppose we have the following dataset of a fruit stall. The dataset contains the sales amount of different fruits for the first five days of the month. Now, we want to know the Month to Date amount for each fruit. In the following example, we will calculate MTD up to the given date in cell C4. We will use a combination of SUM, OFFSET, ROWS, and DAY functions.

Let’s see the steps to perform this action.

STEPS:

• To begin with, select cell H7. Type the following formula in that cell:

• Then, press Enter.
• So, in cell H7, it will tell us the total sales value up to the date ‘3-12-21.’

• After that drag the Fill Handle tool from cell to H10 to get results for other fruits.

• Finally, change the date to ‘4-12-21’ from ‘3-12-21’. We can see that the MTD amount changes automatically.

Read More: Excel Year to Date Sum Based on Month (3 Easy Ways)

2. Calculate MTD in Excel with SUMIF Function & Helper Column

In this method, we will calculate MTD with the SUMIF function. To do this we will have to add helper columns to our dataset. In the following dataset, we have sales data of a fruit stall. It gives us the sales amount for the first 10 days for different fruits. Let’s see the steps to calculate MTD from this dataset.

STEPS:

• First, insert two helper columns with the dataset.

• Next, insert the following formula in cell E7:

• Press, Enter.
• The above formula returns 12 in cell E7.

Here, the IF function returns the value of the month of cell C4 in cell E7 if the value of B7 > C4. Otherwise, the formula will return 0.

• So, if we drag the formula to the end of the dataset we will get a result like the following image.

• Again, insert the following formula in cell F7:

• Press Enter.
• Then, drag the Fill Handle to the end of the dataset.
• So, we can see the results of the above commands in the following image.

Here, in the above formula, the MONTH function gets the value of the month from the date in cells B7 and C4. With the IF formula, it returns the value of cell E7 if the value of B7 and C4 is equal. Otherwise, it will return 0.

• Furthermore, select cell H10. Input the following formula in that cell:

• Hit Enter.
• Finally, get the result in cell H10.

Here, the SUMIF function returns the sum of the range (D7:D16). It is valid until the value by the MONTH function in cell C4 remains within the range (E7:E16).

NOTE:

Here, if we change the date value in cell C4 the MTD amount will change for the updated date accordingly.

Read More: How to Calculate YTD (Year-to-Date) in Excel [8 simple ways]

3. Use Pivot Table & Slicer to Calculate MTD in Excel

Now, we will calculate MDT using a pivot table and a slicer. For this, we will use the given dataset of a fruit stall. The dataset includes fruit sales for the 31 days of December 2021 and the 15 days of January 2022.

In the following image, we have given a part of the dataset. To access the full dataset we will suggest you download the practice workbook added to this article.

Let’s see the steps to do this method.

STEPS:

• Firstly, select any cell from the data range.
• Secondly, go to Insert > Table.
• Check the option ‘My table has headers’ and click on OK.

• The dataset is now in table format.
• Thirdly, add a new column named Day in the dataset.
• Insert the following formula in cell E5:

• Press Enter.
• Double-click on the Fill Handle icon or drag it to the end of the dataset.

• The above commands give us the results like the below image.

Here the DAY function returns the value of a day from a date field.

• Furthermore, select any cell from the data range. We have selected cell B4.
• Then, go to the Insert tab and select the option ‘PivotTable’.

• A new dialogue box will open. Click on OK.

• A section named ‘PivotTable Fields’ like the following image will open in a new worksheet.

• Here, drag the fields Quarters & Years in the Rows section, Price field in the Value section, and Date field in the Columns section.
• After dragging those fields we will get results like the below image.

• In the above image, we can not compare the sales amount of months December and January. Because the result of December month is for 30 days whereas for January it is 15 To compare these two only for 15 days we will add a slicer.
• Go to the ‘PivotTable Analyze’ tab and select the option ‘Insert Slicer’.

• We will get results like the image below. Check the options Day from that dialogue box and click on OK.

• As a result, we get a slicer for calculating days.

• Now, from the excel ribbon input the value 7 in the Columns field.

• So, we will get the slicer for all 30 It will look like a calendar.

• In the end, select the first 15 days from the slicer. We can see that the table displays the sales data only for 15 days for both January and December months.

Read More: How to Calculate Year over Year Growth with Formula in Excel (2 Methods)

Conclusion

In conclusion, by following this tutorial we can easily calculate MTD in excel. To get the best result download the practice workbook added to this article and practice yourself. Feel free to comment in the below box if you face any problems or have any suggestions.

Related Articles

• Growth Over Last Year Formula in Excel (A Step-by-Step Analysis)
• Growth Formula in Excel with Negative Numbers (3 Examples)
• How to Calculate YTD (Year-To-Date) Average in Excel (4 Approaches)