{"id":206349,"date":"2021-03-19T13:24:49","date_gmt":"2021-03-19T10:24:49","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-remove-a-year-from-a-date-in-microsoft-excel\/"},"modified":"2021-03-19T13:24:49","modified_gmt":"2021-03-19T10:24:49","slug":"how-to-remove-a-year-from-a-date-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-remove-a-year-from-a-date-in-microsoft-excel\/","title":{"rendered":"#How to Remove a Year from a Date in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a37a5bac6433\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a37a5bac6433\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-remove-a-year-from-a-date-in-microsoft-excel\/#Custom_Number_Formatting\" >Custom Number Formatting<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-remove-a-year-from-a-date-in-microsoft-excel\/#CONCATENATE_Function\" >CONCATENATE Function<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Remove a Year from a Date in Microsoft Excel&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-435491\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2019\/07\/stock-lede-microsoft-office_excel-1.png\" alt=\"Excel Logo on a gray background\" width=\"650\" height=\"300\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>There are a couple of ways you can remove the year from a date shown in Microsoft Excel. You can use custom cell formatting to hide it or use various functions like CONCATENATE to remove it completely. Here\u2019s how.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Custom_Number_Formatting\"><\/span>Custom Number Formatting<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>While a date value in an Excel cell is technically a number, Microsoft Excel uses a particular type of formatting to display it. The same is true for currency values, where currency symbols are added to values to show that they relate to money. When you add a date in Excel in a format that it recognizes, Excel automatically changes the cell number format to the \u201cDate\u201d type.<\/p>\n<p>You can customize this type to display the date value in different ways. For instance, you can switch between \u201c11\/02\/2021\u201d (in the DD\/MM\/YYYY format commonly used in the U.K.) and \u201c2021-02-11\u201d for 11 February 2021.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-714815 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/02\/Excel-Custom-Date-Formats.png\" alt=\"An example of various custom date formats in Microsoft Excel.\" width=\"341\" height=\"268\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can customize this number format, however, to remove the year entirely from view. The value itself won\u2019t change, but Excel won\u2019t show the year value\u00a0thanks to custom number formatting.<\/p>\n<p>To do this, highlight the cells containing your original date values, then select the \u201cHome\u201d tab on the ribbon bar. From there, select the arrow next to the number format drop-down menu and choose the \u201cMore Number Formats\u201d option.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-714814 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/02\/Excel-More-Number-Formats-Option.png\" alt=\"In Excel, select your date values, then press Home &gt; Number Format &gt; More Number Formats.\" width=\"650\" height=\"477\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the \u201cFormat Cells\u201d menu, select a style of formatting that you like from the \u201cDate\u201d options provided. Once that\u2019s selected, choose the \u201cCustom\u201d option.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-714816 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/02\/Excel-Set-Custom-Date-Format.png\" alt=\"In the &quot;Format Cells&quot; menu, select your date value type from the &quot;Date&quot; menu before pressing the &quot;Custom&quot; option.\" width=\"548\" height=\"346\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the \u201cCustom\u201d option, you\u2019ll see the date number formatting style shown as text in the \u201cType\u201d box. For instance, \u201c11\/02\/2021\u201d would show as \u201cdd\/mm\/yyyy\u201d in this box.<\/p>\n<p>To remove the year, make sure to remove any references to \u201cyyyy\u201d or \u201cyy\u201d in the \u201cType\u201d box as well as any surplus delimiters, such as a dash or forward slash. For instance, \u201c11\/02\/2021\u201d would require you to remove \u201c\/yyyy\u201d from the \u201cdd\/mm\/yyyy\u201d type box, leaving \u201cdd\/mm\u201d in place.<\/p>\n<p>Once you\u2019ve made your changes, select the \u201cOK\u201d button.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-714817 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/02\/Excel-Remove-Year-From-Cell-Formatting.png\" alt=\"Remove &quot;yy&quot; or &quot;yyyy&quot; from the &quot;Type&quot; box, then press &quot;OK&quot; to confirm.\" width=\"522\" height=\"568\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Changing the cell number type will hide the year, but it won\u2019t remove it. If you want to restore the year, change your cell number type to a date type that includes the year again.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"CONCATENATE_Function\"><\/span>CONCATENATE Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Concatenation is a term that basically means to link or combine two or more pieces of data. In Microsoft Excel terms, concatenation can be used to add various text strings, numbers, or cell values together in a new cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-714820\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/02\/Excel-CONCATENATE-Example.png\" alt=\"An example of various text strings combined using the Excel CONCATENATE function.\" width=\"453\" height=\"184\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you want to remove the year from a date in Excel, you could combine the output from two functions (such as DAY or MONTH) together using CONCATENATE. Rather than hiding the year from view using custom number formatting, CONCATENATE (with DAY and MONTH) allows you to create a separate value that doesn\u2019t include the year at all.<\/p>\n<p>To do this, open an Excel workbook containing your date values, or create a new workbook and place date values in separate cells. To use CONCATENATE with DAY and MONTH, insert a new function using this structure, replacing the cell reference (A2) with a reference to the cell containing your date:<\/p>\n<pre>=CONCATENATE(DAY(A2),\"https:\/\/www.howtogeek.com\/\",MONTH(A2))<\/pre>\n<p>The values returned by DAY and MONTH are separated by a special delimiter character, such as a comma or slash, to show that the returned value is a date. Each value (DAY, the delimiter, and MONTH) are separated by a comma in the CONCATENATE function.<\/p>\n<p>In this example, cell A2 contains a date value (11\/12\/2021) in the DD\/MM\/YYYY format. Using CONCATENATE with DAY and MONTH, the day (11) and month (12) from the date value in A2 are placed in another cell separated by the delimiter, which, in this case, is a forward slash.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-714819\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/02\/Excel-CONCATENATE-Examples.png\" alt=\"Examples of the Excel CONCATENATE function used to remove the year from date values.\" width=\"625\" height=\"231\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can customize this formula to change the order of the day and month values (eg. MM\/DD) or to use a different delimiter character. You can also use the fill handle\u00a0to copy this formula into other cells in your column to remove the year from multiple date values.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Automatically Fill Sequential Data into Excel with the Fill Handle<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/714809\/how-to-remove-a-year-from-a-date-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Remove a Year from a Date in Microsoft Excel&#8221; There are a couple of ways you can remove the year from a date shown in Microsoft Excel. You can use custom cell formatting to hide it or use various functions like CONCATENATE to remove it completely. Here\u2019s how. Custom Number Formatting While a&#8230;<\/p>\n","protected":false},"author":1,"featured_media":206350,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2019\/07\/stock-lede-microsoft-office_excel-1.png?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-206349","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/206349","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=206349"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/206349\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/206350"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=206349"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=206349"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=206349"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}