{"id":1977,"date":"2021-06-21T14:18:22","date_gmt":"2021-06-21T14:18:22","guid":{"rendered":"https:\/\/bi.unija.com\/en\/?p=1977"},"modified":"2021-06-21T14:23:28","modified_gmt":"2021-06-21T14:23:28","slug":"excel-calendar-2010-2030-with-holidays","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/","title":{"rendered":"Excel Calendar 2010-2030 With Holidays"},"content":{"rendered":"<p>Identifying holiday dates often appears as challenge in Excel. In this article we list formulas you can use to identify American holidays along with two important days: <strong>Black Friday<\/strong> and <strong>Cyber Monday<\/strong>.<\/p>\n<p>The U.S. government recognizes 10 federal holidays.<\/p>\n<ul>\n<li><strong>January 1<\/strong> (Fixed), New Year&#8217;s Day<\/li>\n<li><strong>January 15\u201321<\/strong> (Floating Monday), Birthday of Martin Luther King, Jr.<\/li>\n<li><strong>February 15\u201321<\/strong> (Floating Monday), Washington&#8217;s Birthday<\/li>\n<li><strong>May 25\u201331<\/strong> (Floating Monday), Memorial Day<\/li>\n<li><strong>July 4<\/strong> (Fixed), Independence Day<\/li>\n<li><strong>September 1\u20137<\/strong> (Floating Monday), Labor Day<\/li>\n<li><strong>October 8\u201314<\/strong> (Floating Monday), Columbus Day<\/li>\n<li><strong>November 11<\/strong> (Fixed), Veterans Day<\/li>\n<li><strong>November 22\u201328<\/strong> (Floating Thursday), Thanksgiving Day<\/li>\n<li><strong>December 25<\/strong> (Fixed), Christmas Day<\/li>\n<\/ul>\n<p>On U.S. federal holidays, non-essential federal government offices are closed, stock market trading is usually suspended, and every federal government employee is paid for the holiday.<\/p>\n<p>Following formulas return the name of the holidays or leave an empty cell. Use each formula in it\u2019s own separate column.<\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>Holiday<\/strong><\/td>\n<td><strong>Formula<\/strong><\/td>\n<\/tr>\n<tr>\n<td>New Year Day<\/td>\n<td>=IF(AND(MONTH(A2)=1;DAY(A2)=1); &#8220;January 1&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Martin Luther King Jr. Day<\/td>\n<td>=IF(A2=(DATE(YEAR(A2); 1; 1)+14+CHOOSE(WEEKDAY(DATE(YEAR(A2); 1; 1)); 1; 0; 6; 5; 4; 3; 2)); &#8220;Martin Luther King Jr. Day&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>President Day<\/td>\n<td>=IF(A2=(DATE(YEAR(A2); 2; 1)+14+CHOOSE(WEEKDAY(DATE(YEAR(A2); 2; 1)); 1; 0; 6; 5; 4; 3; 2)); &#8220;Washington&#8217;s Birthday&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Memorial Day<\/td>\n<td>=IF(A2=(DATE(YEAR(A2);5;31)-CHOOSE(WEEKDAY(DATE(YEAR(A2);5;31));6;0;1;2;3;4;5)); &#8220;Memorial Day&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Independence Day<\/td>\n<td>=IF(AND(MONTH(A2)=7; DAY(A2)=4); &#8220;Independence Day&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Labor Day<\/td>\n<td>=IF(A2=(DATE(YEAR(A2);9;1)+CHOOSE(WEEKDAY(DATE(YEAR(A2);9;1));1;0;6;5; 4; 3; 2)); &#8220;Labor Day&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Columbus Day<\/td>\n<td>=IF(A2=DATE(YEAR(A2);10;1)+7+CHOOSE(WEEKDAY(DATE(YEAR(A2);10;1));1; 0; 6; 5; 4; 3; 2); &#8220;Columbus Day&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Veterans Day<\/td>\n<td>=IF(AND(MONTH(A2)=11; DAY(A2)=11); &#8220;Veterans Day&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Thanksgiving Day<\/td>\n<td>=IF(A2=(DATE(YEAR(A2); 11; 1) + 21 + CHOOSE(WEEKDAY(DATE(YEAR(A2); 11; 1)); 4; 3; 2; 1; 0; 6; 5)); &#8220;Thanksgiving&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Christmas Day<\/td>\n<td>=IF(AND(MONTH(A2)=12; DAY(A2)=25); &#8220;Christmas Day&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>For example, for identifying Martin Luther King Jr. Day we add a column and enter the formula.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"964\" height=\"575\" class=\"wp-image-1978\" src=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/word-image.png\" srcset=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/word-image.png 964w, https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/word-image-300x179.png 300w, https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/word-image-768x458.png 768w\" sizes=\"(max-width: 964px) 100vw, 964px\" \/><\/p>\n<p>For business needs, we are also listing formulas for <strong>Black Friday<\/strong> and <strong>Cyber Monday<\/strong>.<\/p>\n<p><strong>Black Friday<\/strong> is the day after Thanksgiving Day. Many stores offer highly promoted and open very early (sometimes as early as midnight), or some time on Thanksgiving Day. Black Friday has routinely been the busiest shopping day of the year in the United States since at least 2005.<\/p>\n<p><strong>Cyber Monday<\/strong> similarly offers sales and encourages people to shop online. It occurs on Monday after Thanksgiving.<\/p>\n<p>Dates:<\/p>\n<ul>\n<li><strong>November 23\u201329<\/strong> (Floating Friday), day after Thanksgiving Day<\/li>\n<li><strong>November 26\u2013December 2<\/strong> (Floating Friday), Monday after Thanksgiving Day<\/li>\n<\/ul>\n<p>We use the following formulas.<\/p>\n<table>\n<tbody>\n<tr>\n<td>Black Friday<\/td>\n<td>=IF(A2=(DATE(YEAR(A2); 11; 1) + 21 + CHOOSE(WEEKDAY(DATE(YEAR(A2); 11; 1));<\/p>\n<p>5; 4; 3; 2; 1; 0; 6)); &#8220;Black Friday&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<tr>\n<td>Cyber Monday<\/td>\n<td>=IF(A2=DATE(YEAR(A2);11;1)+21<\/p>\n<p>+CHOOSE(WEEKDAY(DATE(YEAR(A2);11;1));4;3;2;1;0;6;5)<\/p>\n<p>+CHOOSE(WEEKDAY(DATE(YEAR(A2);11;1)+21<\/p>\n<p>+CHOOSE(WEEKDAY(DATE(YEAR(A2);11;1));4;3;2;1;0;6;5)); 1; 0; 6; 5; 4; 3; 2); &#8220;Cyber Monday&#8221;; &#8220;&#8221;)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Identifying holiday dates often appears as challenge in Excel. In this article we list formulas you can use to identify American holidays along with two important days: Black Friday and Cyber Monday. The U.S. government recognizes 10 federal holidays. January<\/p>\n","protected":false},"author":5,"featured_media":1979,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1977","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Excel Calendar 2010-2030 With Holidays - Bi Unija<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel Calendar 2010-2030 With Holidays - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"Identifying holiday dates often appears as challenge in Excel. In this article we list formulas you can use to identify American holidays along with two important days: Black Friday and Cyber Monday. The U.S. government recognizes 10 federal holidays. January\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2021-06-21T14:18:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-06-21T14:23:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/\",\"url\":\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/\",\"name\":\"Excel Calendar 2010-2030 With Holidays - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png\",\"datePublished\":\"2021-06-21T14:18:22+00:00\",\"dateModified\":\"2021-06-21T14:23:28+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Calendar 2010-2030 With Holidays\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel Calendar 2010-2030 With Holidays - Bi Unija","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/","og_locale":"en_US","og_type":"article","og_title":"Excel Calendar 2010-2030 With Holidays - Bi Unija","og_description":"Identifying holiday dates often appears as challenge in Excel. In this article we list formulas you can use to identify American holidays along with two important days: Black Friday and Cyber Monday. The U.S. government recognizes 10 federal holidays. January","og_url":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/","og_site_name":"Bi Unija","article_published_time":"2021-06-21T14:18:22+00:00","article_modified_time":"2021-06-21T14:23:28+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png","type":"image\/png"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/","url":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/","name":"Excel Calendar 2010-2030 With Holidays - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png","datePublished":"2021-06-21T14:18:22+00:00","dateModified":"2021-06-21T14:23:28+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2021\/06\/44.png","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/excel-calendar-2010-2030-with-holidays\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Excel Calendar 2010-2030 With Holidays"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1977"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1977"}],"version-history":[{"count":12,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1977\/revisions"}],"predecessor-version":[{"id":1991,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1977\/revisions\/1991"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1979"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1977"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1977"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1977"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}