Quite simply, you enter numbers representing a year, a month and a day and the formula returns these values in a date format. For example, DATE(1985, 12, 25) returns \u201c12\/25\/1985\u201d.<\/span><\/p>\n This seems really basic, so you might be wondering why you\u2019d ever need this function at all. Sometimes looks can be deceiving, and this is one of those times.<\/span><\/p>\n Date formats vary from country to country, the two most common formats being \u201cDD\/MM\/YYYY\u201d and \u201cMM\/DD\/YYYY\u201d. But there are also other ways to express dates that include words as well as numbers.<\/span><\/p>\n DATE standardizes all of this, you have to write the number of the year followed by the number of the month, followed by the number of the day. Even when you use this formula you can still choose how dates are shown in your spreadsheet. Go to Format > Number > More formats > More date and time formats.<\/span><\/p>\n Typing DATE into your formula is a quick and reliable way to tell Google Sheets that you are entering a date. An alternative is to use quotes but you must remember to include them correctly and to enter the numbers in the correct order.<\/span><\/p>\n You might run across datasets that include separate columns for year, month and day. You can have Google Sheets make a date column, just use the DATE function and merge the values into a single column.<\/span><\/p>\n Understanding how spreadsheets manage dates is crucial for effectively working with dates. It is really quite simple: spreadsheets treat dates as whole numbers. Format is then applied to these numbers to make them readable.<\/span><\/p>\n This numerical approach is what enables formulas involving dates to calculate durations, deadlines, and schedules accurately. By treating dates as numbers, you can easily compare them, calculate the number of days between two of them, or even add a certain number of days to a given date.<\/span><\/p>\n It is not necessary to know exactly how this system works, but it is important how dates are stored. If you ever run into a whole number where a date should be, now you know the reason.<\/span><\/p>\n The syntax for this formula is particularly simple. You just have to feed it three mandatory pieces of information: year, month and day.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =DATE(year, month, day)<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can also include cell references as parameters as in =DATE(A4, B4, C4).<\/span><\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button 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” global_colors_info=”{}” global_module=”255434″ theme_builder_area=”post_content”]<\/p>\n One tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The image below has a couple of examples of the formula in action.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/DATE-formula-in-Google-Sheets-1.png” alt=”DATE formula in Google Sheets 1″ title_text=”DATE formula in Google Sheets 1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Note that for the examples in row 2,3 and 4, the outputs are \u2018numbers\u2019 while the examples in row 5,6 and 7 are \u2018dates\u2019. This is to illustrate that the DATE formula can be used for both.<\/span><\/p>\n The example in row 2 has numbers and returns a corresponding value for that date. The parameters for the examples in row 3, 4, 6 and 7 are references to cells that contain year, month and date values. Accordingly, we get the output dates.<\/span><\/p>\n The example in row 5 shows that the DATE function in Google Sheets can also use formulas as its parameters.\u00a0 It still returns a date as expected.<\/span><\/p>\n What happens if you enter a value that is not usually expected in a date format? Here I have typed in 16 for the month parameter. Then, in the next example, I have entered 86 for the date parameter. You might expect it to return an error, but surprisingly, it doesn\u2019t. Take a look at the image below:<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/DATE-formula-in-Google-Sheets-2.png” alt=”DATE formula in Google Sheets 2″ title_text=”DATE formula in Google Sheets 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n These results are unexpected. I\u2019ll explain why Google Sheets returned these values.<\/span><\/p>\n Because there are only 12 months in a year, Google Sheets carries on counting into the following year. So if you enter 2012 for the year and 13 for the month, Google Sheets counts into the following year. This means that the “13th month of 2012” refers to the first month of 2013: January.<\/span><\/p>\n If you look at the first example: DATE(2017, 16, 21) there is no 16th month in a year. Google Sheets counts into the next year, and the result is the 4th month of 2018 (A. The result is April 21 2018.<\/span><\/p>\n Similar logic applies to the second example. Again, there is no 16th month so the formula counts into the following year. But there is no 86th day in April. Google Sheets continues counting into the following months: May and June. As a result, DATE(2017,16,86) returns the value June 25 2018. This is because June 25 is 86 days after April began.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Dealing with dates and dated values in spreadsheets can be a headache. This is just one formula for date but there are plenty more. Get useful tips on how to <\/span>calculate the difference between dates in Google Sheets<\/span><\/a> and <\/span>how to use EDATE formula<\/span><\/a>. <\/span><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" In spreadsheets, working with dates can be a hassle. Sometimes date formats are not properly recognized, and to use dates in formulas you have to enclose them in quotes. There are plenty of Google Sheets date functions, but the most basic is DATE. Quite simply, you enter numbers representing a year, a month and a […]<\/p>\n","protected":false},"author":40,"featured_media":4373,"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 DATE<\/a> formula in Google Sheets accepts a given date through year, month and day component values, and returns a date type value.\nUses of the DATE function<\/span><\/h2>\n
Regional date formatting<\/h3>\n
Include dates in formulas<\/h3>\n
Quickly retrieve values from dates in large datasets<\/h3>\n
How spreadsheets handle dates<\/span><\/h2>\n
Syntax<\/span><\/h2>\n
<\/ul>\n
\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\nHow to use the DATE function in Google Sheets<\/h3>\n
Managing dates in Google Sheets<\/b><\/h3>\n
Syntax<\/h3>\nDATE(year, month, day)<\/strong><\/span>\n\nThe year, month and day parameters are self-explanatory parameters that accept corresponding components of a date. These can be direct values or references to cells that have corresponding values. Or they can also be\n
Usage: DATE Formula in Google Sheets<\/h3>\nUsing this formula is a pretty straight forward process. Let us understand the formula using the following examples.\n\n
![\"DATE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-14.png\")
\n\nPlease notice that the first three outputs are 'number' formatted, while the last three are 'date' formatted. This is done deliberately, just to illustrate that a date is a number after all.\n\nThe first example takes in direct numbers and returns a corresponding date value. The parameters for the second, third, fifth and sixth examples are references to cells that contain year, month and date values. Accordingly, we get the output dates.\n\nThe fifth example shows that the DATE formula can also accept other appropriate formulas for its parameters. And, it returns yet another date as expected.\n\nWhat happens if we try and input a value that is not usually expected? For example, let us key in 16 for month parameter. Then, in the next example, try entering 86 for the date parameter. We may expect that it returns an error, but surprisingly, it doesn't. Please have a look at the image below.\n\n![\"DATE](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-15.png\")
\n\nLet us understand the first case. If we carefully observe, the year and month used in the first formula are 2017 and 16 respectively. But the resulting output year and month show 2018 and 4 respectively. Let's think about this.The formula DATE(2017,1<\/strong><\/span><\/mark>,21) evaluates to 21-Jan-2017. Similarly DATE(2017,12<\/strong><\/span><\/mark>,21) evaluates to 21-Dec-2017, which is 11 months (i.e. 12<\/strong><\/span><\/mark>-1<\/strong><\/span><\/mark>) ahead of 21-Jan-2017. Extending this logically further, DATE(2017,16,21) should be 15 months (i.e. 16-1) ahead of 21-Jan-2017, therefore we have 21-Apr-2018 as our output.\n\nSimilar logic applies to the second example. Here DATE(2017,16,86) is 15 months (i.e. 16-1) and 85 days (i.e. 86-1) ahead of DATE(2017,1,1), therefore we have the output 25-Jun-2016.","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[55,39,28],"class_list":["post-4177","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-ben-collins","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/4177","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=4177"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/4177\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/4373"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=4177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=4177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=4177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}