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.

**Table of Contents**Expand

## 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.

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

### 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.

**STEPS:**

- To begin with, select cell
**H7**and type the following formula.

`=SUM(OFFSET($C$6,ROWS($B$7:C7),0,,DAY($C$4)))`

- Then, press
**Enter**. - In cell
**H7**, we will get the total sales value up to the date 3-12-21.

- After that drag the
**Fill Handle**tool from cell**H7**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.

**ðŸ”Ž**** How Does the Formula Work?**

**OFFSET($C$6,ROWS($B$7:C7),0,,DAY($C$4)):**This part returns the range. The range is specified for row**7**taking cell**C6**and the date of cell**C4**as references.**SUM(OFFSET($C$6,ROWS($B$7:C7),0,,DAY($C$4))):**This part returns the sum of sales amount up to the date in cell**C4**.

**Read More:** How to Calculate YTD (Year-to-Date) in Excel

### 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 for 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.

- Insert the following formula in cell
**E7**.

`=IF(B7<$C$4,MONTH($C$4),0)`

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

Here,Â **the IF function **returns the month number of cell **C4 **in cell **E7 **if **B7 < C4**. Otherwise, the formula will return 0.

- We drag the formula to the end of the dataset and get a result like the following image.

- Again, insert the following formula in cell
**F7**.

`=IF(MONTH(B7)=MONTH($C$4),E7,0)`

- 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.

In the above formula, **the MONTH function** gets the value of the month from the date in cells **B7** and **C4**. 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**and input the following formula.

`=SUMIF($E$7:$E$16,MONTH($C$4),D7:D16)`

- 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.

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

Now, we will calculate *MDT *by inserting 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.

**STEPS:**

- Firstly, select any cell from the data range.
- Then, go to
**Insert >****Table**. - Check the option
**My table has headers**Â and click on**OK**.

- The dataset is now in table format.
- Now, add a new column named
*Day*in the dataset. - Insert the following formula in cell
**E5**.

`=DAY(B5)`

- 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*. 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 days. 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.

**Download Practice Workbook**

We can download the practice workbook from here.

## 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

- How to Calculate YTD (Year-To-Date) Average in Excel
- How to Calculate YTD (Year to Date) in Excel
- How to Sum Year to Date Based on Month in Excel

**<< Go Back to YTD Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel**