How to Create a Formula to Increase a Date by 1 Month

PDF download Download Article
PDF download Download Article

Microsoft Excel is a powerful program. If you are using it for a calendar or anything else where you might need to find the date exactly 1, 2, or 3 months after another date here is a formula you can use.

Things You Should Know

  • The DATE function makes it easy to increment dates by days, months, or years.
  • When editing a formula with the DATE function, you can simply add +1 to the month.
  • You can also use the EDATE function to increase the date by one month.


Steps

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/d\/d8\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-1.jpg\/v4-460px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-1.jpg","bigUrl":"\/images\/thumb\/d\/d8\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-1.jpg\/aid68099-v4-728px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/4\/43\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-2.jpg\/v4-460px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-2.jpg","bigUrl":"\/images\/thumb\/4\/43\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-2.jpg\/aid68099-v4-728px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    Ex. 1/1/2006
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/c\/cb\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-3.jpg\/v4-460px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-3.jpg","bigUrl":"\/images\/thumb\/c\/cb\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-3.jpg\/aid68099-v4-728px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-3.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    Ex. 5
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/d\/d7\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-4.jpg\/v4-460px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-4.jpg","bigUrl":"\/images\/thumb\/d\/d7\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-4.jpg\/aid68099-v4-728px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-4.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}

    =DATE(YEAR( A2 ),MONTH( A2 )+ B2 ,MIN(DAY( A2 ),DAY(DATE(YEAR( A2 ),MONTH( A2 )+ B2 +1,0))))replacing what is in bold with the appropriate cells for your spreadsheet.
  5. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/9\/94\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-5.jpg\/v4-460px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-5.jpg","bigUrl":"\/images\/thumb\/9\/94\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-5.jpg\/aid68099-v4-728px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-5.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}
  6. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/7\/73\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-6.jpg\/v4-460px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-6.jpg","bigUrl":"\/images\/thumb\/7\/73\/Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-6.jpg\/aid68099-v4-728px-Create-a-Formula-to-Increase-a-Date-by-1-Month-Step-6.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/creativecommons.org\/licenses\/by-nc-sa\/3.0\/\">Creative Commons<\/a><br>\n<\/p><p><br \/>\n<\/p><\/div>"}
    Another option is to use =EDATE(start_date,months)
    This is a simpler method but can only be used if the Analysis ToolPak add-in is installed.
    7. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      • Calculating the Day is complicated due to the varying 30 and 31 day months. This formula will take a date like 10/31/2006 and add 1 month to become 11/30/2006. Whereas, a more simple formula without the MIN function may give the incorrect date of 12/1/2006.
        Thanks
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      You Might Also Like

      How to
      Create a Calendar in Microsoft Excel
      How to
      Convert Measurements Easily in Microsoft Excel
      How to Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Create a Currency Converter With Microsoft Excel
      How to
      Create a Mortgage Calculator With Microsoft Excel
      4 Easy Ways to Add the Time and Date Automatically in Sheets
      How to Create an IF-THEN Formula in Excel: Basic Syntax, Multiple Conditions, & More
      The 3 Easiest Ways to Sort by Number in Google Sheets
      How to Make a Google Spreadsheet: Beginners Guide
      3 Easy Ways to Get Rid of Dotted Lines in Microsoft Excel
      How to
      Add the Same Value to Multiple Cells in Excel
      How to
      Add Rows to a Pivot Table
      How to Add Custom Calculated Fields to Pivot Tables in Excel
      How to Use Google Sheets: The Ultimate Guide for Getting Started
      Advertisement

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 333,699 times.

      Is this article up to date?

      Advertisement