{"id":3402,"date":"2021-06-12T15:11:30","date_gmt":"2021-06-12T22:11:30","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3402"},"modified":"2021-06-12T15:11:32","modified_gmt":"2021-06-12T22:11:32","slug":"how-to-use-excel-edate-function","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3402","title":{"rendered":"How to Use Excel EDATE Function"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"674\" height=\"339\" src=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/06\/Edate.png\" alt=\"\" class=\"wp-image-3403\" title=\"Excel EDATE function\" srcset=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/06\/Edate.png 674w, https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/06\/Edate-300x151.png 300w\" sizes=\"(max-width: 674px) 100vw, 674px\" \/><\/figure>\n\n\n\n<p>Summary&nbsp;<\/p>\n\n\n\n<p>The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the future, and a negative value for dates in the past. \u00a0<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>start_date<\/strong>&nbsp;&#8211; Start date as a valid Excel date.<\/li><li><strong>months<\/strong>&nbsp;&#8211; Number of months before or after start_date.<\/li><\/ul>\n\n\n\n<p>The EDATE function can add or subtract whole months from a date.\u00a0You can use EDATE to calculate expiration dates, contract dates, due dates, anniversary dates, retirement dates, and other dates in the future or past. The EDATE function takes two\u00a0arguments:\u00a0<em>start_date<\/em>\u00a0and\u00a0<em>months<\/em>.\u00a0<em>Start_date<\/em>\u00a0must be a valid\u00a0Excel date. The\u00a0<em>months<\/em>\u00a0argument specifies\u00a0how many months in the future or past to move\u00a0\u2013 use a positive number to move forward in time, and a\u00a0negative number to move back in time.<\/p>\n\n\n\n<p>EDATE will return a\u00a0serial number corresponding to a date. To display the result as date, apply a\u00a0number format of your choice.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example #1 &#8211; Basic usage<\/h3>\n\n\n\n<p>If A1 contains the date February 1, 2018, you can use EDATE like this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=EDATE(A1,1) \/\/ returns March 1, 2018\n=EDATE(A1,3) \/\/ returns May 1, 2018\n=EDATE(A1,-1) \/\/ returns January 1, 2018\n=EDATE(A1,-2) \/\/ returns December 1, 2017<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Example #2 &#8211; 6 months from today<\/h3>\n\n\n\n<p>To use EDATE with today&#8217;s date, you can combine with\u00a0the\u00a0TODAY function. For example, to create a date exactly 6 months from today, you can use:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=EDATE(TODAY(),6) \/\/ 6 months from today<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Example #3 &#8211; Move by years<\/h3>\n\n\n\n<p>To use the EDATE function to move by years, multiply by 12. For example, to move a date forward 2 years, you&nbsp;can use either of&nbsp;these formulas:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=EDATE(A1,24) \/\/ forward 2 years\n=EDATE(A1,2*12) \/\/ forward 2 years<\/pre>\n\n\n\n<p>The second form is handy when you already have a value for years in another cell and want to convert to months inside EDATE.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example #4 &#8211; End of month<\/h3>\n\n\n\n<p>EDATE is clever about rolling &#8220;end of month&#8221; dates forwards or backwards, and will adjust year, month, and day values as necessary. For example EDATE will maintain the last day of month when a day is 31:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=EDATE(\"31-Jan-2019\",1) \/\/ returns 28-Feb-2019\n=EDATE(\"31-Jan-2019\",2) \/\/ returns 31-Mar-2019\n=EDATE(\"31-Jan-2019\",3) \/\/ returns 30-Apr-2019\n=EDATE(\"31-Jan-2019\",4) \/\/ returns 31-May-2019\n=EDATE(\"31-Jan-2019\",5) \/\/ returns 30-Jun-2019<\/pre>\n\n\n\n<p>EDATE will also respect leap years:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=EDATE(\"31-Jan-2020\",1) \/\/ returns 29-Feb-2020<\/pre>\n\n\n\n<p>However, EDATE will not maintain an end of month when the day value is less than 31. For example:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=EDATE(\"28-Feb-2019\",1) \/\/ returns 28-Mar-2019<\/pre>\n\n\n\n<p>If an end-of-month date is a requirement, the\u00a0EOMONTH function\u00a0is a better option.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example #5 &#8211; EDATE with time<\/h3>\n\n\n\n<p>The EDATE function will strip times from a dates that include time (sometimes called a &#8220;datetime&#8221;). To preserve the time in a date, you can use a formula like this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=EDATE(A1,n)+MOD(A1,1)<\/pre>\n\n\n\n<p>Here, the MOD function is used to\u00a0extract the time from the date\u00a0in A1 and add it back to the result from EDATE.<\/p>\n\n\n\n<p>See below for more examples of formulas that use the EDATE function.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Notes<\/h3>\n\n\n\n<ol class=\"wp-block-list\"><li>EDATE will return the\u00a0 #VALUE error if the start date is not a valid date.<\/li><li>If the start date has a\u00a0fractional time\u00a0attached, it will be removed.<\/li><li>If the months argument contains a decimal value, it will be removed.<\/li><li>To calculate an end of month date, see the\u00a0EOMONTH function.<\/li><li>EDATE returns a\u00a0date serial number, which must be\u00a0formatted as a date.<\/li><\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Summary&nbsp; The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the future, and a negative value for dates <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3402\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[762,763,760],"class_list":["post-3402","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-edate-function","tag-excel-edate","tag-excel-edate-function"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3402","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3402"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3402\/revisions"}],"predecessor-version":[{"id":3404,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3402\/revisions\/3404"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3402"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3402"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3402"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}