Tag: Excel Month

How to Sum Values Based on Month and Year in Excel

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product number or total costs etc. If we want to calculate the total values based on a certain month of a year, for example if we want to calculate the total sales for January 2020, how can we do? This article will help you to solve this problem.

See the example below. A column lists some dates. B column lists the sales. E column lists the month and year we want to calculate the total sales for. F column is used for showing the returned value by formula.

Sum Values Based on Month 1

1. Sum Values Based on Month and Year by SUMIF Function

Step 1: In cell F2, enter the formula:

 =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,1),A2:A16,"<="&DATE(2019,1,31))

In SUMIFS function, B2:B16 is the sum range, A2:A16 is the criteria range. “>=”&DATE(2019,1,1) and “<=”&DATE(2019,1,31) are the two criteria. Details please see below screenshot.

Sum Values Based on Month 2

Verify that total sales 1500 is displayed for January 2019 after calculation.

Sum Values Based on Month 3

Step 2: If we want to calculate total sales based on a certain period, we can change the parameters in DATE function. For example, to calculate the total sales for period 1/3/2019 – 1/3/2020, we can enter the formula:

 =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,3),A2:A16,"<="&DATE(2020,1,3))
Sum Values Based on Month 4

Above all, you can change the date in DATE function to do sum per your demands.

2. Sum Values Based on Month and Year by SUMPRODUCT

Step 1: In cell F2, enter the formula

=SUMPRODUCT((MONTH(A2:A16)=1)*(YEAR(A2:A16)=2019)*(B2:B16))

In this formula we also use MONTH function and YEAR function to filter date from range A2:A16 based on criteria ‘Date is included in period January 2019’. B2:B16 is the sum range.

Sum Values Based on Month 5

Step 2: Click Enter to get the result. Verify that we get the same result in method #1 step#1.

Sum Values Based on Month 6

3. Sum Values Based on Month and Year with User Defined Function (VBA Code)

If you want to sum values based on Month and Year with User Defined Function in Excel, you can use these steps:

Step1: Open the Visual Basic Editor by pressing Alt + F11.

Step2: In the Visual Basic Editor, click on “Insert” from the menu bar and select “Module” to create a new module.

Step3: In the new module, enter the following code, save the module with a suitable name.

Function SumValuesByMonthAndYear(sumRange As Range, dateRange As Range, sumMonth As Integer, sumYear As Integer) As Double
    Dim i As Integer
    Dim sum As Double
    
    For i = 1 To sumRange.Rows.Count
        If Month(dateRange.Cells(i, 1).Value) = sumMonth And Year(dateRange.Cells(i, 1).Value) = sumYear Then
            sum = sum + sumRange.Cells(i, 1).Value
        End If
    Next i
    
    SumValuesByMonthAndYear = sum
    
End Function

Step4: go back to your Excel worksheet and enter the following formula without the quotes into a blank cell.

=SumValuesByMonthAndYear(B2:B16, A2:A16, 1, 2019)

This function takes four arguments:

  • sumRange – The range of cells containing the values to sum
  • dateRange – The range of cells containing the dates to check for the month and year
  • sumMonth – The month to sum for (as an integer from 1 to 12)
  • sumYear – The year to sum for (as a four-digit integer)

The function then loops through the dateRange, checks if each date matches the sumMonth and sumYear, and adds the corresponding value from the sumRange to the sum variable.

Finally, the function returns the sum variable as the result.

How to Sum Values Based on Month and Year vba 2.png

4. Video: Sum Values Based on Month and Year

This video will demonstrate how to Sum Values Based on Month and Year in Excel using the Formula and VBA Code.

5. Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

How to Split Date into Day, Month and Year in Excel

If you have a date column in your Excel spreadsheet and need to separate it into day, month, and year, you may be wondering how to do so efficiently. This post will guide you through four different methods for splitting a date into day, month and year using Excel formulas, Text to Columns, Flash Fill, and VBA Code. How do I quickly split date as Day, Month and Year using Formulas or Text to Columns feature in Excel.

1. Split Date into Day Month and Year with Formulas

Assuming that you have a list of data in range A1:A6, which contain date values. And you want to split each date value into three separate columns for day, month and year. How to achieve it. You can use three formulas based on the DAY function, the Month function and the Year function to achieve the result of splitting dates. Just do the following steps:

Step1: type Day string in a blank cell in a new column, such as: B1, and type Month string in Cell C1, type Year string in Cell D1.

split date into day month year1

Step2: Type the following formula in Cell B2, and press Enter key, then drag the AutoFill Handle over to other cells to apply this formula.

=DAY(A2)
split date into day month year2

This formula will extract Day Value from the given date in range A2:A6.

Step3: Type the following formula based on the Month function to get Month value from date in Cell C2, and press Enter key, and then drag the AutoFill Handle over to other cells to apply this formula.

=MONTH(A2)
split date into day month year3

This formula will extract Month value from the given date in range A2:A6

Step4: Type the following formula in Cell D3 to get the Year value, and press Enter key, and then drag the AutoFill Handle over other cells.

=Year(A2)
split date into day month year4

You should see that all dates in range A2:A6 have been split into separate day, month and year.

2. Split Date into Day Month and Year with Text to Column Feature

You can also use Text to Column options to achieve the same result of splitting date into separate day, month and year in three columns. You just need to do the following steps:

Step1: select the range of cells that you want to split.

split date into day month year5

Step2: go to DATA tab, click Text to Columns command under Sort & Filter group. And the Convert Text to Columns Wizard dialog will open.

split date into day month year6

Step3: select Delimited as the file type, and click Next button.

split date into day month year7

Step4: only check Other checkbox under Delimiter section, type the delimiter / into the Other text box, and click Next button.

split date into day month year8

Step5: select one Destination cell, such as: B2, click Finish button.

split date into day month year9

Step6: the date column has been split into separate day, month and year.

split date into day month year10

3. Split Date into Day, Month and Year with Flash Fill Feature

Flash Fill can quickly and easily split a date column into day, month, and year components in Excel. Just do the following steps:

Step1: First select three new columns to the right of the date column.

Step2: In the first new column, enter the first value you want to extract. For example, the day value.

Step3: In the second new column, enter the second value you want to extract. For example, the month value.

Step4: In the third new column, enter the third value you want to extract. For example, the year value.

How to Split Date into Day, Month and Year flash fill 1.png

Step5: Click on the first day value in the first new column, then click on Flash Fill command under Data Tools group in Data tab.

How to Split Date into Day, Month and Year flash fill 2.png

Step6: Excel will automatically show the suggested day values in the remaining cells of the new column.

How to Split Date into Day, Month and Year flash fill3.png

Step7: Repeat step 6 for the remaining two new columns to extract the month and year values.

How to Split Date into Day, Month and Year flash fill4.png

Once you have extracted all three values, you can delete the original date column and the temporary columns containing the extracted values.

How to Split Date into Day, Month and Year flash fill5.png

4. Split Date into Day, Month and Year with VBA Code

You can also use VBA code to split a date into day, month, and year components in Excel. Just do the following steps:

Step1: Open your Excel workbook and press ALT + F11 to open the Visual Basic Editor.

Step2: Click on “Insert” from the top menu and choose “Module” to create a new module.

Step3: Copy and paste the following code into the module, Save the module and close the Visual Basic Editor.

How to Split Date into Day, Month and Year vba code1.png
Sub SplitDate_excelgeek()
    Dim dateRange As Range
    Set dateRange = Application.InputBox("Select the range of cells containing the dates to split", Type:=8)
    Dim cell As Range
    For Each cell In dateRange
        Dim dayValue As Integer
        Dim monthValue As Integer
        Dim yearValue As Integer
        dayValue = Day(cell.Value)
        monthValue = Month(cell.Value)
        yearValue = Year(cell.Value)
        cell.Offset(0, 1).Value = dayValue
        cell.Offset(0, 2).Value = monthValue
        cell.Offset(0, 3).Value = yearValue
    Next cell
End Sub

Step4: Click on “Macros” under the “Code” group. Select the “SplitDate_excelgeek” macro from the list and click on “Run“.

How to Split Date into Day, Month and Year vba code2.png

Step5: select the range of cells containing the date values that you want to split. For example, select range of cells A2:A5.

How to Split Date into Day, Month and Year vba code3.png

Step6: The VBA Macro will split the selected date column into day, month, and year components and place the results in the adjacent columns.

How to Split Date into Day, Month and Year vba code4.png

5. Video: Split Date into Day Month and Year

This video tutorial will guide you on how to split a date into day, month, and year components using various methods in Excel.

6. Conclusion

Whether you need to work with a large dataset or just a few dates, these methods can help you quickly and accurately separate your date values into separate columns.

7. Related Functions

  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…

Add Months To Date In Excel

It is important to adjust time periods when performing financial modeling. In Excel, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months you want to add. Here will show you how to add months to date in excel and even provide some examples that might be useful!

Method1: Add Months to Date using EDATE Function

Add Months To Date1

The generic formula is:

=EDATE(Sart_date, months)
  • Start_date: the starting date on which you want to add months
  • Months: the month that you wish to add

Return Value:

The function will return the date that is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

When you enter the MONTH function, Excel looks at the date in the cell that you specify as the start_date argument. It then adds the number of months that you entered as the second argument to that date. If you enter a negative number for the second argument, Excel subtracts that number of months from the start_date.

To add a number of months is placed at B2 and add months to the date in cell A2. The formula for this would be:

=EDATE(A1,B1)

Add Months To Date1

Or simply enter the number you want in your formula, and it will be replaced with that value.

=EDATE(A1,10)

Add Months To Date1

Explanation

This will add 10 months to the date in cell A1. The function will return the date that is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

Add Months To Date1

You can also format the date in a certain way you want by right-clicking on it and then choosing ‘Format Cells’. In this pipping format, dialog select one date format type you want to display results. Then, press the Apply button to save changes made after filling out fields with proper data values following desired specifications, including today’s date if necessary!

Add Months To Date1

The EDATE function is a great way to find out your next date. Simply enter any valid date and then select how many months away from now you want the new one! It’s really easy; just feed it with an amount less than or equal to the number of desired days between these two points-and voila.

Note:

Excel stores data as sequential serial numbers, which can be used in calculations. For instance, if you want to find out what day of the week it was on January 1st, 1900, then simply enter “serial#” into your formula bar and press Enter! The result will show that this date falls exactly 120 years ago – having been achieved by entering 44219 days after 1900 (which would make sense considering we’re talking about a Gregorian calendar).

Method2: Add Months to Date using Date Function

The generic formula is:

=DATE(YEAR(date),MONTH(date))+months,DAY(date)))

Arguments

  • Date: The starting date on which you want to add months.
  • Months: Months are important when adding or subtracting a certain number of months from/to dates. They add the indicated amount, depending on whether it’s positive and negative values respectively; for example, -1 means one month less than what you want (in other words: ago).
  • YEAR(date): The year component of the date in Date.
  • MONTH(date): The month component of the date in Date.
  • DAY(date): The day component of the date in Date.

Return Value:

A date is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

How This Formula Work

To add months to the date in cell A1, and a number of added is placed at B1. The formula for this would be as follows:

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

Add Months To Date1

With the Year, Month, and Day functions, you can easily create your date from any combination of these three values. With the Date function, you can create dates based on any year and month of your choice.

Related Functions

  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel EDATE function
    TThe Excel EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

 

Add Days to Date in MS Excel

If you are an vavid MS Excel user, then you might have come across situations where you need to add the same or different days into the particular date, and if you have done this task manually, then let me add that it’s the most impractical way to do it, especially when MS Excel has the functionality to do it in just a matter of seconds.

So by reading this article carefully, you would get to know different ways to add days into the Date in MS Excel in a few seconds.

So let’s dive into it,

Ways To Add Days To Date

There are three most excessively used ways to add days to date in MS Excel, stated as follows:

1.    Adding Days to Dates by Value

2.    Adding Days from a specific Cell Location

3.    Adding Days using the DATE() Function

1. Adding Days to Dates by Value

Assume that you want to add a predetermined number of days (say, 10) to each date in column A and save the result in column F as shown below:

add days to date1

 So for this, you need to do the steps are given below:

Step1: Please select the first cell of the column where you want to save the result (in our example, cell F2).

Step2: Enter ‘=‘ and then choose the first cell in the column holding the dates to which you want to add days (cell A2).

Step3: Then, enter ‘+‘ followed by the number of days you wish to add. So, to add 10 days, enter ‘+10‘ in the same box. This suggests that the formula for cell F2 should be =A2+10.

Step4: On your keyboard, press the Enter In cell F2, you will obtain the result of adding 10 days to the date.

add days to date1

Step5: If you want to add the same number of days to all of the dates in Column A, then just pull down the fill handle (at the bottom right of cell F2) to replicate the formula to all of the rows in Column F.

add days to date1

2.  Adding Days From A Specific Cell Location

If you want to add a different number of days to each date, then put the number of days (which is needed to add) in a separate column for each row (as shown below).

add days to date1

Then, follow the instructions outlined below:

Step1: Please select the first cell of the column where you want to save the result (in our example, cell G2).

Step2: Enter ‘=‘ and then choose the first cell in the column holding the dates to which you wish to add days (cell A2).

Step3: After that, type ‘+‘.

Step4: Then, in our example, choose the first cell in the column showing the number of days to add (cell F2). This suggests that the formula in cell G2 should be =A2+F2.

Step5: On your keyboard, press the Enter Add the number of days in cell F2 to the date in cell A2 to get the result.

add days to date1

Step6: Drag down the fill handle (located at the bottom right of cell G2) to duplicate the formula to all Column G’s rows. This will add the cell in column F to the cell in column A for each row.

add days to date1

Note: Your result may show as a serial number rather than a date in rare situations. This may occur if the format of your result cell is Number or Text. In such circumstances, you may easily convert the serial to a Date format using the following formula:

  • Select the cell or the entire column using the right mouse button.
  • From the popup menu, choose Format Cells. The Format Cells dialogue box will appear.
  • On the Number tab, choose the date from the Category drop-down menu.
  • From the Type list on the right side of the dialogue box, choose the format in which you want your date to display.
  • Select OK.

This would change the format of all your cells to date.

3. Using the DATE() Function to Add Days

There is another method for adding days to a date in Excel. This is accomplished by using the DATE function. Let’s utilize the same dataset as we used before:

add days to date1

What you must do is as follows:

Step1: Please select the first cell of the column where you want to save the result (in our example, cell F2).

Step2: Fill up the blanks using the following formula: =DATE(YEAR(A2), MONTH(A2), DAY(A2)+10)

Step3: On your keyboard, press the Enter In cell A2, you will obtain the result of adding 10 days to the date.

add days to date1

Step4: Drag down the fill handle (located at the bottom right of cell F2) to replicate the formula to all of Column F’s rows.

add days to date1

You may even add months and years to your date using this approach. Assume you wish to add one year, three months, and four  days to a date. You may simply achieve this using the approach described above.

Simply add 1 to the first parameter, 3 to the second, and 4 to the third, and your formula becomes:

=DATE(YEAR(A2)+1, MONTH(A2)+3, DAY(A2)+4)

add days to date1

Related Functions

  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

Filter Data By Date Field

The use of Microsoft Excel’s Format Cells and Auto Filter option is a great way to filter data by year or Month. This article will discuss how to filter by date field using Filter function and Format cells in Microsoft excel.

Filter Data by Date Field using FILTER Function

The General Formula is as below:

=FILTER(Total_data,MONTH(range2)=5,"Not Found")

filter by date field1

Summary

FILTER function can be used with one of Excel’s date functions to make your data more relevant. In this example shown below, E2 is used:

=FILTER(A2:C9,MONTH(B2:B9)=5,"Not Found")

filter by date field1

This is a great way to only show data from one specific Month. For example, it would be easy to want our report for May only! We need to get this effect out of the filter engine. (A2:C9) and then add “date” after that with B2:B9 named ranges, so no other periods show up when looking through your findings.

Clarification:

The MONTH function is used to create an argument for the FILTER function that filters based on a logical test created with ONE of many other functions available in Excel. The array data contains all information, without any headers or spaces between values, perfect if you want every record exactly how it’s shown!

=MONTH(B2:B9)

filter by date field1

Month contains a range from B2:B9. Since this is an array with 8 cells, MONTH returns 8 results!

=MONTH(B2:B9)=5

filter by date field1

The “include” argument is a set of values determining which results will be included in the array. These are compared to 5, and this operation creates an ordered list with TRUE or FALSE as its elements before delivering it on through filter function call.

Only when the result is TRUE will it appear in this final output. The “if_empty” argument has been set to ‘ Not Found ‘ so as not to show any unmatched records or values.”

Filter By Month And Year

To filter by Month and year, use the following formula:

=FILTER(A2:C9,(MONTH(B2:B9)=5)*(YEAR(B2:B9)=2022),"Not Found")

filter by date field1

The values for Month and year can easily be replaced with cell references.

Filter the Data by Date using Format Cell

Now we’ll look at how to filter the data by date. We have information in range A1: C9, with dates represented as columns on each line representing who purchased what from us over time.

filter by date field1

To change the filter for a specific month or year, follow these steps:

Step1: Copy A1:A9 and paste it right next to column A

Step2: Click on the range A1:A9 and select

Step3: To copy the date, press Control+C on your keyboard.

filter by date field1

Step4: Select cell A1 and press Ctrl+Shift+”+”

filter by date field1

Step5: The Insert Dialog Box will appear.

Step6: To continue editing the data, choose Shift Cells right and click the Ok button.

filter by date field1

Step7: To increase the range of cells that you can paste into, copy and then press Ctrl+ shift +. Then select shift cells right in the insert dialog box by clicking OK.

filter by date field1

Step8: A new month and year are just around the corner, so it’s time to update your headlines!

filter by date field1

Step9: Select the column of Month and then press ctrl+1 keys to open the format cells dialog box

Step10: Once the dialog box appears, then select custom in the number tab.

filter by date field1

Step11: Enter mm (to show Month’s full name) in the type box and click on OK.

filter by date field1

Step12: Now select the Column of Year and press the Ctrl+1 to open the format cells dialog box.

Step13: Once the dialog box appears, select custom in the number tab.

Step14: In order to show the full year, just enter YYYY in this box and click on.

filter by date field1

Step15: The output is a simple list that can be sorted by either Month or year.

filter by date field1

Step16: You can also use this trick to filter any cell quickly. Select the desired range and press Ctrl+Shift+L.

If you want to filter the data according to a specific month or year, select cell A1 and press Ctrl+Shift+L.

filter by date field1

Conclusion:

We’ve given you the tools to filter your data in Microsoft Excel by date fields in this blog post. Now that you know how easy it is to do, why not try adding a few more filters to your workbook? You may be surprised what insights can come from applying multiple filters at once!

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

How to Sum Data by Month Ignore Year in Excel

Sometimes we may want to sum data by month but ignoring the year. For example, if we want to investigate which month has the top sales among several years, we may sum data by month but ignoring the year information. Actually, we can use formula based on SUMPRODUCT function and MONTH function to sum data for this instance. In this tutorial, we will introduce the usage of SUMPRODUCT function and MONTH function with simple description, formula explanation and necessary screenshots, also teach you the way to sum data by month ignoring year with above two functions.

EXAMPLE

Obviously, we have data for October in year 2020 and year 2019. We want to sum all items for October.

FORMULA:

In D2, enter the formula =SUMPRODUCT((MONTH($A$2:$A$11)=10)*$B$2:$B$11).

HOW THIS FORMULA WORKS:

Inside of SUMPRODUCT function, we also use MONTH function in this instance. For MONTH function, the syntax is MONTH(serial_number). For example, if A1 is 01/01/2020, then MONTH(A1) returns 1. This function returns the month serial number. So, if we want to find out date in date range belongs to October, we need to use MONTH($A$2:$A$11)=10 here, thus we can get an array as result.

Enter =(MONTH(A2:A11)=10) in D2 temporally. We get TRUE here, actually it only returns the first value of the array.

Move focus in formula bar, then press F9 (on different version maybe Fn+F9). We can see the array is listed properly.

Update formula to =–(MONTH(A2:A11)=10). Then TRUE or FALSE will convert to number properly.

Move focus in formula bar, then press F9.

For SUMPRODUCT function, the syntax is SUMPRODUCT(array1,[array2],[array3],…). After learning MONTH function, we all know that MONTH($A$2:$A$11)=10 it will return an array. As SUMPRODUCT function can returns the sum of an array, and $B$2:$B$11 is another array for saving amount, and data in this range is also the match value for each date in A column. so here we can use =SUMPRODUCT ((MONTH($A$2:$A$11)=10)*$B$2:$B$11) to sum data by month October properly. It equals to {1;1;1;0;0;0;0;1;1;1}*{100;200;100;300;150;100;50;100;50;100}, equivalent to =SUMPRODUCT({100;200;100;0;0;0;0;100;50;100}).

RESULT:

Now we can sum data by month ignoring the year properly.     

Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…

How to Round Off Date to the Nearest Month in Excel

Sometimes we may get some dates in excel and we want to calculate their nearest month based on date. Actually, this can be implemented by applying formula in excel. This tutorial will introduce you the formula to round off date to its nearest month.

Precondition:

Create a list contains some dates.

How to Round Off Date to the Nearest Month 1

Method: Round Off Date to the Nearest Month by Formula

Step 1: In B2 enter the formula =MONTH(EOMONTH(A2,(DAY(A2)>15)+0)).

In this formula, EOMONTH function returns the date of the last day of a month before or after the specified date; MONTH function returns an integer between 1 and 12 which represents the month in a year. Use the combination of above two functions can round off date to its nearest month.

How to Round Off Date to the Nearest Month 2

Step 2: Press Enter to get returned value. Verify that for date 1/4/2020, the nearest month is January.

How to Round Off Date to the Nearest Month 3

Step 3: Drag down the fill handle till reaching the end of the list. Verify that all the nearest months are calculated properly.

How to Round Off Date to the Nearest Month 4

Comment:

  1. This formula only returns an approximate value, no matter how many days a month, the nearest month for date >15 is the next month.
  2. For special month like February, you can adjust the formula to match your request, for example if you want to get 3 (March) for date>13, you can update your formula =MONTH(EOMONTH(A4,(DAY(A4)>13)+0)).

 

Related Functions

  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…

How to Convert Date & Time Format to Date in Excel

Sometimes we want to convert date and time format to date only format in excel for example convert 01/29/2019 06:51:03 to 01/29/2019, we can convert format by Formula or Format Settings. The two ways are easy to learn, so you can select one you like to convert date and time format.

Convert Date & Time to Date by Formula

Prepare a table with date and time, the format is MM/DD/YYYY HH/MM/SS.

Convert Date & Time Format to Date 1

If we want to convert them to date format, we can follow below steps:

Step 1: In B2, enter the formula =MONTH(A2) & “/” & DAY(A2) & “/” & YEAR(A2).

Convert Date & Time Format to Date 2

Step 2: Click Enter to get the result. Verify that format is changed to date properly, time part is cleared.

Convert Date & Time Format to Date 3

Step 3: Drag the Fill Handle down to fill B3. Verify that B3 is changed to date format properly.

Convert Date & Time Format to Date 4

Convert Date & Time to Date by Format Settings

Step 1: On A2, right click to load menu, select Format Cells.

Convert Date & Time Format to Date 5

Step 2: In Format Cells window, under Number tab, in Category list, select Date; then in Type list select one format you want to show the date. Then the date format is displayed in Sample field.

Convert Date & Time Format to Date 6

Step 3: Click OK. Verify that previous date and time is changed to date format.

Convert Date & Time Format to Date 7

Related Functions

  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…

 

 

 

How to Delete or Remove Year from a Date in Excel

This post will guide you how to delete or remove year from a given standard date in Excel. How do I remove the year from a date with a formula in Excel.

Remove Year from a Date

Assuming that you have a list of data in range B1:B5, in which contain date values. And you want only display the month and Day parts. So you need to remove year from cells. How to do it. You can use a formula based on the MONTH function and the DAY function to achieve the result.  Like this:

=MONTH(B1)&"-"&DAY(B1)

Or

=CONCATENATE(MONTH(B1),"-",DAY(B1))

Type one of the above formula into cell C1, and press Enter key. And then drag the AutoFill handle from Cell C1 to C5.

delete year from date1

You should notice that the year part has been removed for each date in range B1:B5.

You can also use the TEXT function to create a formula to achieve the same result. Like this:

=TEXT(B1,"mm-dd")

Type one of the above formula into cell D1, and press Enter key. And then drag the AutoFill handle from Cell D1 to D5.

delete year from date2

Related Functions

  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

How to sort Dates by Month and Day Only in Excel

This post will guide you how to sort dates or birthdays by month or day only in Excel. How do I sort dates by Year or Month or Day only with a formula in Excel 2013/2016. How to use the MONTH function and DAY function to sort dates by month or day only in Excel.

Sort Dates by Month

Assuming that you have a list of data in range A1:B5, which contain date values, and you need to sort those dates by month only. How to achieve it. You can use the MONTH function in combination with a helper column to achieve the result. Just do the following steps:

#1 type the following formula in the adjacent cell of Cell B1, then press Enter key to apply this formula.

=MONTH(B1)

sort dates by month1

#2 drag the AutoFill Handle over to other cells to apply this formula to get the month value from dates.

sort dates by month2

#3 select all Month values in helper column. And go to DATA tab, click Sort Smallest to Largest or Sort Largest to Smallest button under Sort & Filter group. The Sort Warning dialog will open.

sort dates by month3

#4 select Enable the selection radio button, and click Sort button. You will see that the dates have been sorted by month only. And you can now delete the helper column.

sort dates by month4

sort dates by month5

Sort Dates by Year

If you want to sort dates by Year only, you can repeat the above steps, just only use the following formula to instead of Month formula.

=YEAR(B1)

sort dates by month6

Sort Dates by Day

If you want to sort dates by Day only, you can also repeat the above steps, and just only use the following formula instead of the MONTH formula.

=DAY(B1)

sort dates by month7

Video: Sort Dates by Year/Month/Day

 

Related Functions

  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…

 

How to Add the Current Month or Year in a Cell in Excel

This post will guide you how to add the current month or year into a cell or header or footer in your worksheet. How do I insert the current date or time in a cell with a formula in Excel.

Add Current Month or Year Using Short Keys

Assuming that you want to insert current year or month into a cell or header in Excel, you can use one short cut to achieve the result. Just do the following steps:

#1 you can press Ctrl + ; keys to insert the current date in a cell.

add current date in cell1

#2 press one space to insert one blank character, and then press Ctrl + Shift +; to insert the current time in that cell.

add current date in cell2

Add Current Month or Year Using Formula

You can also use a formula to insert the current year, month, day or a date or time in one cell.

Insert current Year

To insert the current year into a cell or footer or header in Excel, you can use a formula based on the YEAR function and the TODAY function. Just like this:

=YEAR(TODAY())

add current date in cell3

Type this formula into a blank cell or footer or header in your worksheet, and then press Enter key in your keyboard.

Insert Current Month

If you want to insert the current month, you can use a formula based on the MONTH function and the TODAY function to achieve the result. Like this:

=MONTH(TODAY())

add current date in cell4

Insert Current Day

To Insert the current day in a cell, you can use the below formula based on the TODAY function.

=TODAY()

add current date in cell5

Insert Current Date and Time

If you want to insert the current date and time into one cell, you can directly use the NOW function. Like this:

=NOW()

add current date in cell6

Related Functions

  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
  • Excel NOW function
    The Excel NOW function returns the serial number of the current date and time. And it will be updated each time when your worksheet is changed or opened.The syntax of the NOW function is as below:=now()…

Converting Dates to Fiscal Quarters and Years

This post will guide you how to convert dates to fiscal quarters or years in Excel. How do I get fiscal quarter from a given date in Excel. How to calculate the fiscal year from a date in Excel. How to convert date to fiscal year or month with a formula in Excel.

If  you have a list of data in range B1:B5 which contain dates, and you want to calculate the fiscal quarters and years in your worksheet, how to achieve it. You can refer to the following introduction to achieve the result.

Assuming that the start month of the fiscal year in your company is April.

Converting Dates to Fiscal Quarters

To calculate one date to a Fiscal quarters in your worksheet, you can use a formula based on the CHOOSE function and the MONTH function. Just like this:

=CHOOSE(MONTH(B1),4,4,4,1,1,1,2,2,2,3,3,3)

Select Cell C1, and type this formula into it, and press Enter key in your keyboard, and then drag the AutoFill Handle over other cells to apply this formula.

convert dates to fiscal year quarters

The Fiscal quarters have been calculated.

Converting Dates to Fiscal Years

To convert dates in cells to Fiscal years, you can create a formula based on the YEAR function and the MONTH function. Just like the following formula:

=YEAR(B1)+(MONTH(B1)>=4)

Type this formula in Cell C1, and press Enter key, and then drag the AutoFill Handle over other cells to apply this formula.

convert dates to fiscal year quarters2

The Fiscal Years have been converted successfully in your worksheet.

Related Functions

  • Excel Choose Function
    The Excel CHOOSE function returns a value from a list of values. The CHOOSE function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_numbe…

Count Dates in Given Year/Month/Day in Excel

This post will guide you how to count dates in a give year or month or day with a formula in Excel. How do I count cells based on year, month or day in Excel. How to countif by a specified date (Year or month) in a range of cells in Excel.

Count Dates in Given Year

Assuming that you have a list of data in range B1:B6 that contain date values, and you want to count those dates by a specified year(2018), how to achieve it. or you want to count the dates by a specified month (5).

You can create a formula based on the SUMPRODUCT function and the YEAR function to count dates by a give year. Just like this:

=SUMPRODUCT(1*(YEAR(B1:B6)=2018))

Type this formula into a blank cell and then press Enter key to apply this formula.

count dates in given year1

The count number is calculated by this formula in Cell C1.

You can also use another excel array formula based on the SUM function , the IF function and the YEAR function to achieve the same result.

=SUM(IF(YEAR(B1:B6)=2018,1))

The Year function will convert the data value to a year value only. and the IF function will check if the year value returned by the YEAR function is equal to the given year, if so, return value 1. otherwise, returns nothing. and the sum function will sum the array list that returned by the IF function.

You need to type this formula into a cell, and then press Ctrl + Shift + Enter keys in your keyboard to convert this formula as a array formula.

count dates in given year2

Count Dates in a given Month

To count dates in a given month, you can create a formula based on the SUMPRODUCT function and the Month function. For example, you want to count datas in range of cells B1:B6 by a given month value 5. you can write down this formula:

=SUMPRODUCT(1*(MONTH(B1:B6)=5))

count dates in given year3

Or you can also write down an excel array formula based on the SUM function, the IF function and the MONTH function. Just like this:

=SUM(IF(MONTH(B1:B6)=5,1))

count dates in given year4

Count dates in a given date(year,month, day)

If you want to count the number of a give date (9/7/2018) in a range of cells (B1:B6) in Excel, You can use a formula based on the COUNTIF function. Just like this:

=COUNTIF(B1:B6,"9/7/2018")

count dates in given year6

Count Cells between Dates

If you want to count the number of cells that contain dates between two specified dates, You can still use the COUNTIFS function to create a formula to achieve the result.
For example, you want to count the number of cells that fall between two dates(from 1/1/2017 to 10/1/2018), and you can use a fromula based on the countif function. Like this:

=COUNTIFS(B1:B6,">"&"1/1/2017",B1:B6,"<"&"10/1/2018")

    count dates in given year5

Related Functions

  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_numbe…
  • Excel COUNTIFS function
    The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

Convert Month Name to Number

This post will guide you how to convert month name to number in excel. How do I convert month name to number with an excel formula. How to convert month name to number with an Excel VBA macro. How to convert 3 letter month abbreviation to number in excel. How to convert 3 character month to number in excel.

Assuming that you have a list of data B1:B12 contain month names and you want to convert all month names to numbers, how to achieve it.

Convert Month Name to Number with Excel Formula

If you want to convert month name to number with an excel formula, or you want to convert 3 letter month name to numbers, you can create a formual based on the MONTH function and the DATEVALUE function. Like this:

=MONTH(DATEVALUE(B1&"1"))

Type this formula into the formula box of a blank cell C1, then drag the AutoFill handler over other cells to apply this formula.

convert month name to number1

You will see that all month names have been converted to numbers in range of cells C1:C12.

Convert Month Name to Number with VBA code

You can also use an excel VBA Macro to convert month name to number in excel. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

convert month name to number2

Sub convertMonthNameToNumber()
    Dim myRange As Range
    Dim selectedRange As Range
    windowTitle = "convert month name to numbers"
    Set selectedRange = Application.Selection
    Set selectedRange = Application.InputBox("Select one Range that contain month names", windowTitle, selectedRange.Address, Type:=8)
    For Each myRange In selectedRange
        If myRange.Value <> "" Then
            myRange.Value = Month(DateValue("03/" & myRange.Value & "/2018"))
        End If
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

convert month name to number3

#6 select cells that you want to convert, such as: B1:B12. Click Ok button.

convert month name to number4

#7 Let’s see the result.

convert month name to number5

Convert Number to Month Name with Excel Formula

If you want to conert number to month name, you can use an formula based on the TEXT function, and the DATE function to achieve it. Like this:

=TEXT(DATE(2018,B1,1),"mmmm")

Type this formula into a blank cell, and then press enter key, then drag the AutoFill Handler over other cells.

Let’s see the result.

convert month name to number6

Related Functions

  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

 

 

Excel MONTH Function

This post will guide you how to use Excel MONTH function with syntax and examples in Microsoft excel.

Description

The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. And you can use the MONTH function to get a month component from a given date value. And you can feed the month number to another formula.

The MONTH function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The MONTH function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the MONTH function is as below:

=MONTH (serial_number)

Where the MONTH function arguments is:

  • Serial_number -This is a required argument. A valid date value that contains the month component you want to extract. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE (2019,3,15) for the 15th day of March, 2019.

Note:

  • A serial date is how the Microsoft Excel stores dates and it represents the number of days since 1900-01-01, so the January 1, 1900 date is serial number 1 by default.
  • If the serial_number is not a valid Excel date, it will return #VALUE! Error.

Excel MONTH Function Examples

The below examples will show you how to use Excel MONTH Function to return an integer number that representing the month component of a given Excel date

1# get month of the date in Cell A1, enter the following formula in Cell B1.

=MONTH(A1)

excel month examples1

2# convert Month name to number, type the following formula in Cell B1.

You should use the DATEVALUE function to convert a date stored as text to a serial number. Then feed the result into the MONTH function to extract a month number from date.

=MONTH(DATEVALUE(A1 & "1"))

excel month examples2

Related Functions

  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31). The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…

More Excel Month Function Examples

  • Convert Month Name to Number
    If you want to convert month name to number with an excel formula, or you want to convert 3 letter month name to numbers, you can create a formual based on the MONTH function and the DATEVALUE function…
  • Count Dates in Given Year/Month/Day in Excel
    You can create a formula based on the SUMPRODUCT function and the YEAR function to count dates by a give year….

Excel TODAY Function

This post will guide you how to use Excel TODAY function with syntax and examples in Microsoft excel.

Description

The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The serial number is the date-time code used by Excel for date and time calculations.

You can use the TODAY function to get the current date displayed on your worksheet, and regardless of when you open the workbook. And it also can be used for calculating intervals. For example, if you want to know that person’s age (he was born in 1984), you can use the following formula:

=YEAR(TODAY())-1984

This formula will use the TODAY function as an argument for the YEAR function to get the current year, and then subtracts 1984, getting his age.

The TODAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The TODAY function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the TODAY function is as below:

=TODAY()

There are no any arguments for the TODAY function, but you still need to use empty parentheses ().

Excel TODAY Function Examples

The below examples will show you how to use Excel TODAY Function to return the current system date.

1# using the TODAY function to get the current date, enter the following formula in Cell B1.

=TODAY()

excel TODAY examples1

2# Get the current date and then subtracts 10 days, using the following formula:

=TODAY() -10

excel TODAY examples2

3# Get the number of days between the current date and 03/14/2019, type the following formula:

=DATEVALUE("03/14/2019")-TODAY()

excel TODAY examples3

4# Get the current day of the month, using the following formula:

=DAY(TODAY())

excel TODAY examples4

5# Get the current month of the year, type the following formula in Cell B1:

=MONTH(TODAY())

excel TODAY examples5

You can press F9 key to force the worksheet to recalculate each time and then update the value. If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates. On the File tab, click Options, and then in the Formulas category under Calculation options, make sure that Automatic is selected.

excel TODAY examples6

Related Functions

  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31). The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…

More Excel TODAY Function Examples

  • Conditional Formatting date with red Amber or Green
    How to highlight the date with red if the cell dates is past now(). How to highlight the date with amber if the cell date is past now but within the next 6 months from now(). How to highlight the date with green if the cell date is more than 6 months from now…
  • Highlight Rows If Dates Have Passed
    Assuming that you have a worksheet that have a bunch of columns and each row has a date column. If the date in the date column goes past from now on and you want to highlight the entire row with red color….
  • Count Dates in Given Year/Month/Day in Excel
    You can create a formula based on the SUMPRODUCT function and the YEAR function to count dates by a give year….

How to Add / Subtract Days, Months and Years to Date

This post will guide you how to add or subtract a given number of days to a date in excel. How to add or subtract weeks to a date using excel formula. How to add or subtract a certain number of months to a date using excel formula. How to add or subtracts a given number of years to a date using an excel formula.  And how to add or subtract a combination of years, months and days to a date.

Assuming that you have a list of date in your worksheets, and you want to perform some arithmetic operations (such as, addition or subtraction) to calculate different time units, such as, you want to add or subtract a number of days to or from a date by using a formula to adjust the date according to your needs.

Add / subtract days to date

If you have a list of date in a range or worksheet, and you want to add or subtract a certain number of days to the date in the range, you can refer to the following general formula:

=Date + days

Or

=Date - days

For example, to add 100 days to a date in Cell B1, then you can write the following formula in Cell C1:

=B1 +100

To subtract 100 days from a date in Cell B1, then you can write the following formula in Cell D1

=B1-100

If you want to add or subtract 100 days to a date in Cell b1, then you can use the TODAY function to build the following formula:

=Today() +100

Or

=Today() – 100

Let’s see the below screenshot to see the returned result by the above formulas:

add days to date1

Add / subtract weeks to date

Assuming that you want to add or subtract a given number of weeks to a certain date in A2, then you can refer to the following generic formula:

=Date +N Weeks * 7  

Or

=Date – N weeks*7

You need to multiply the number of weeks by 7 to get the total number of days, then you can perform arithmetic operations in the same time unit (days).

Let’s see the below examples:

Supposing that you want to add or subtract 5 weeks to a date in A2, you can refer to the following excel formulas:

=A2 + 5*7

=A2 – 5*7

=TODAY() + 5*7

=TODAY() – 5*7

Let’s see the returned results by the above formula from the below screenshot:

add or subtract weeks to date1

Add / subtract months to date

If you want to add or subtract a given number of whole months to a date, you can use the EDATE function to create a generic formula as follows:

=EDATE(start_date, months)

Start_date – the start date

Months – the number of months that you want to add or subtract

If you want to subtract months, you just need to enter a negative number as the months argument in the EDATE function.

Assuming that you want to add or subtract 3 months to the date in A2, you can use the following formula:

=EDATE(A2,3)

=EDATE(A2,-3)

=EDATE(TODAY(),3)

=EDATE(TODAY(),-3)

Let’s see the last result for above formulas:

add or subtract months to date1

Add / subtract years to date

If you want to add or subtract a given number of years to a date, you can create an excel formula based on the DATE function, the YEAR function, the MONTH function and the DAY function.

The generic formula is as follows:

To add years to a date in excel:

=DATE(YEAR(date)+ years, MONTH(date),DAY(date))

To subtract years to a date in excel:

=DATE(YEAR(date)+ years, MONTH(date),DAY(date))

Assuming that you want to add or subtract 2 years to a date in A2, you can use the following formulas:

=DATE(YEAR(A2)+ 2, MONTH(A2),DAY(A2))

or

=DATE(YEAR(A2)- 2, MONTH(A2),DAY(A2))

Let’s see how this formula works:

=YEAR(A2)  

Result: 2018

The YEAR function returns the year component of a date in A2.

=MONTH(A2)

Result: 2

The MONTH function returns the month portion of a date in A2.

=DAY(A2)

Result: 19

The DAY function returns the day of component of a date in A2.

Let’s see the following screenshot to show the result for above DATE formulas: add or subtract years to date1

Add / subtract a combination of days, months and years

If you want to add or subtract a combination of days, months and years to a date in a single formula, you can also use a combination of the DATE function, the YEAR function, the MONTH function and the DAY function.

To add days, months and years in excel:

=DATE(YEAR(start_date) + years, MONTH(start_date) + months, DAY(start_date) + days)

To subtract days, months and years

=DATE(YEAR(start_date) - years, MONTH(start_date) - months, DAY(start_date) - days)

Assuming that you want to add or subtract 2 years, 3 months and 100 days to or from a date in cell A2, you can use the following formulas:

=DATE(YEAR(A2) + 2, MONTH(A2) + 3, DAY(A2) + 100)

Or

=DATE(YEAR(A2) – 2, MONTH(A2) – 3, DAY(A2) – 100)

Then enter the above formula into Cell C2 or C3, then let’s see the returned results:

add or subtract combination day month year1

Related Formulas

  • Convert date to month and year only in excel
    If you want to convert the date to month and year only, you can use “yyyymm” format code within the TEXT function in excel, so you can write down the below TEXT formula:=TEXT(date,”yyyymm”)
  • Convert date to month and day only in excel
    If you want to convert the date (mm/dd/yyyy) to month and day only, you can use “mm dd” format code within the TEXT function in excel, so you can write down the below TEXT formula: =TEXT(date,”mm dd”)
  • Convet Text Date dd/mm/yy to mm/dd/yyyy as Date Format
    If you want to convert dates in the text format to the date format as mm/dd/yyyy, you can use a combination of the DATE function, the VALUE function, the RIGHT function, the MID function, and the LEFT function to create a new excel formula…

Related Functions

  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
  • Excel EDATE function
    TThe Excel EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…