[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h2>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=EOMONTH(start_date, months)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- start_date<\/strong> \u2013 is the date that the EOMONTH function calculates the last date of a month from.<\/li>\n
- months<\/strong> \u2013 is the number of months that the function should move either in forward or backward in time, corresponding to a positive value or a negative value respectively.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use the EOMONTH function<\/h2>\n
I\u2019ll demonstrate the function using a few examples. Please consider the snapshot below.<\/p>\n
Note that the Result column has been formatted to \u201cdd-mmm-yyyy\u201d for the purposes of avoiding ambiguity (given that the date notations varies across countries).<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/EOMonth-image-1-1-1.png” alt=”eomonth-function-google-sheets-1″ title_text=”eomonth-function-google-sheets-1″ align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In the first example case, the start_date parameter takes in the output that the DATE() function returned. And I have set the months parameter to \u20180\u2019, meaning that the function will not move the date forward or backwards in time. Therefore, the function evaluates the end date within the month to 31-Mar-2020.<\/p>\n
Let\u2019s take a look at the second case. At the time of writing, the date was 14-Nov-2020 (which the TODAY() function evaluated to in the second example). Factoring a shift of 15 days added to the TODAY() function, along with a value of 12 for months parameter, gives the last date of the month as 30-Nov-2021.<\/p>\n
The third example is pretty similar, except for the start_date parameter I provided a reference to the cell that has a date.<\/p>\n
Unlike the previous three cases, the start_date for the fourth case is an absolute number. I also keyed in a negative value for the months parameter. The EOMONTH function calculates the last date by shifting the time from the date value 42,846 (equivalent to 21-Apr-2017) back by nine months.<\/p>\n
[\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\nAutomate data transfers between spreadsheets
<\/span><\/p>\nFind out how<\/a><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Cases of interest<\/h3>\n
Let us experiment a little further and input text based date values for the start_date<\/strong> parameter.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/EOMonth-image-2-1.png” alt=”eomonth-function-google-sheets-2″ title_text=”eomonth-function-google-sheets-2″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In the first example, the function trims the decimal values and uses only the integer values. Accordingly, it evaluates the last date of the month. Of course, it cannot accept negative numbers, as there is no date interpretation for such values.<\/p>\n
From the next three examples, you can see that the EOMONTH function accepts input date strings in the \u201cdd-mmm-yyyy\u201d and \u201cdd-mm-yyyy\u201d formats. But it will generate an error with \u201cmm-dd-yyyy\u201d date format strings.<\/p>\n
Use case: BOMONTH anybody?<\/h4>\n
Is there a counterpart to EOMONTH, something like BOMONTH (that stands for B<\/strong>eginning O<\/strong>f MONTH<\/strong>)? Apparently, no! But fortunately, you can make use of the EOMONTH function to build a virtual BOMONTH function, as shown in the snapshot below.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/EOMonth-image-3-1.png” alt=”eomonth-function-google-sheets-3″ title_text=”eomonth-function-google-sheets-3″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In this example, I\u2019ve added a +1 at the end of the function. This adds 1 day, pushing the date from the last day of the month to the first day of the next month. Because this takes the date to the next month, you also have to add -1 as the months parameter. This will shift the function back 1 month, and give you the date of the first day of the current month. The formula here is =EOMONTH(TODAY(),-1)+1<\/strong>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Use case: last Monday of the month<\/h4>\n
Since the work week is typically Monday to Friday, it can be helpful in some cases to be able to find the last Monday of every month. In the screenshot below, I\u2019ve done just that.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/EOMonth-image-4-1.png” alt=”eomonth-function-google-sheets-4″ title_text=”eomonth-function-google-sheets-4″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The WEEKDAY function is used here to get the day of the week that the last day of the month falls on. I’ve used numbers 1 through 7 (1 being Monday, 2 is Tuesday, etc.). By subtracting the weekday and adding 1, you can get the last Monday of the month. The formula for this is =EOMONTH(“5-Dec-2020”,0)-(WEEKDAY(EOMONTH(“5-Dec-2020”,0),2))+1<\/strong>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
If we need to know the date of a specified number of months before or after a given date, we use the\u00a0EDATE function in Google Sheets<\/a>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
The EOMONTH function in Google Sheets stands for \u201cEnd Of MONTH\u201c, and rightly so. It returns the last calendar date of a month that is a specified number of months behind or ahead of a given date.Syntax=EOMONTH(start_date, months) start_date \u2013 is the date that the EOMONTH function calculates the last date of a month from. […]<\/p>\n","protected":false},"author":40,"featured_media":29469,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The EOMONTH<\/a> formula notation in Google Sheets stands for \"E<\/strong>nd O<\/strong>f MONTH<\/strong>\", and rightly so. It returns the last calendar date of a month that is a specified number of months behind or ahead of a given date.\n
Syntax<\/h3>\nEOMONTH(start_date, months)<\/strong><\/span>\n
- \n \t
- start_date<\/strong><\/span> - is the date that the EOMONTH formula calculates the last date of a month from.<\/li>\n \t
- months<\/strong><\/span> - is the number of months that the formula should move either in forward or backward in time, corresponding to a positive value or a negative value respectively.<\/li>\n<\/ul>\n
Usage: EOMONTH formula<\/h3>\nLet us understand it's working by making use of a few examples. Please consider the snapshot below. Please note that we have preset the Result column format to \"dd-mmm-yyyy\", for the purposes of avoiding ambiguity (given that the date notations varies across countries).\n\n
![\"EOMONTH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-24.png\")
\n\nIn the first example case, the start_date<\/strong><\/span> parameter takes in the output that the DATE() function returned. And we have set the months<\/strong><\/span> parameter to '0'. Therefore, the formula evaluates the end date within the month to 31-Mar-2017.\n\nLet us take a look at the second case. At the time of writing this post, the date was 21-Apr-2017 (which, the TODAY() function evaluated to in the second example). Factoring a shift of 15 days we added to the TODAY() function, along with a value of 12 for months<\/strong><\/span> parameter, we get the last date of the month as 31-May-2018.\n\nThe third example is pretty similar, except for the start_date<\/strong><\/span> parameter, we provided a reference to the cell that has a date.\n\nUnlike the previous three cases, the start_date<\/strong><\/span> for the fourth case is an absolute number. And also, we have keyed in a negative value for the month's<\/strong><\/span> parameter. The EOMONTH formula calculates the last date by shifting the time from the date value 42,846 (equivalent to 21-Apr-2017) back by nine months.\nCases of interest<\/h5>\nLet us experiment a little further and input text based date values for the start_date<\/strong><\/span> parameter.\n\n
![\"EOMONTH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-25.png\")
\n\nIn the first example, the formula truncates the decimal values and uses only the integer values and accordingly evaluates the last date of the month. Of course, it cannot accept negative numbers, as there is no date interpretation for such values.\n\nFrom the next three examples, it seems that the EOMONTH formula is okay with accepting input date strings in the \"dd-mmm-yyyy\" and \"mm-dd-yyyy\" formats. But it will throw up an error with \"dd-mm-yyyy\" date format strings.\nUse case: BOMONTH anybody?<\/h5>\nIs there a counterpart to EOMONTH, something like BOMONTH (that stands for B<\/strong>eginning O<\/strong>f MONTH<\/strong>)? Apparently, no! But fortunately, we can make use of the EOMONTH formula to build a virtual BOMONTH formula, as shown in the snapshot below.\n\n
![\"EOMONTH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-26.png\")
\n\n ","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-4101","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/4101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=4101"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/4101\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/29469"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=4101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=4101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=4101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - months<\/strong><\/span> - is the number of months that the formula should move either in forward or backward in time, corresponding to a positive value or a negative value respectively.<\/li>\n<\/ul>\n
- months<\/strong> \u2013 is the number of months that the function should move either in forward or backward in time, corresponding to a positive value or a negative value respectively.<\/li>\n<\/ul>\n