{"id":388708,"date":"2022-01-02T22:00:30","date_gmt":"2022-01-02T19:00:30","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-conditional-formatting-based-on-date-in-google-sheets\/"},"modified":"2022-01-02T22:00:30","modified_gmt":"2022-01-02T19:00:30","slug":"how-to-use-conditional-formatting-based-on-date-in-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-conditional-formatting-based-on-date-in-google-sheets\/","title":{"rendered":"#How to Use Conditional Formatting Based on Date in Google Sheets"},"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-6a32975695429\" 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-6a32975695429\" 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-use-conditional-formatting-based-on-date-in-google-sheets\/#Set_Up_a_Conditional_Formatting_Rule_for_a_Date\" >Set Up a Conditional Formatting Rule for a Date<\/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-use-conditional-formatting-based-on-date-in-google-sheets\/#Edit_Delete_or_Add_Another_Rule_in_Google_Sheets\" >Edit, Delete, or Add Another Rule in Google Sheets<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Use Conditional Formatting Based on Date in Google Sheets&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731741\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>With conditional formatting, you can highlight data on your spreadsheet automatically. If you have a sheet where you need the dates easier to spot, you can use conditional formatting in Google Sheets based on date.<\/p>\n<p>You might have a spreadsheet containing due dates for bills or deadlines for project tasks. You can make dates like those past due or coming up soon pop and easy to see. Whether a column, row, or cell, you simply set up the conditional formatting rule, and when the data matches, you\u2019ll see the highlight you choose <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ly.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Create a Gantt Chart in Google Sheets<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Set_Up_a_Conditional_Formatting_Rule_for_a_Date\"><\/span>Set Up a Conditional Formatting Rule for a Date<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Head to Google Sheets, sign in, and open the spreadsheet you want to use. Select the cells that you want to highlight with conditional formatting. This can be an entire column or row, or a cell or cell range.<\/p>\n<p>Click Format &gt; Conditional Formatting from the menu. This displays the sidebar for you to set up the rule.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-773894\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/FormatConditionalFormatting-GoogleSheetsConditionalFormattingDate.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select Format, Conditional Formatting\" width=\"460\" height=\"465\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Be sure you\u2019re on the Single Color tab at the top and then confirm the cell selection in the Apply To Range field.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-773893\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/ApplyToRange-GoogleSheetsConditionalFormattingDate.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Confirm the data range\" width=\"295\" height=\"203\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Click the \u201cFormat Cells If\u201d drop-down box and choose \u201cDate Is,\u201d \u201cDate Is Before,\u201d or \u201cDate Is After\u201d per your preference. The option you select will determine what appears in the selection below it.<\/p>\n<p>You can then choose from options like today, tomorrow, or yesterday. Or you can pick a larger date range like in the last week, month, or year. You can also use an exact date if you like.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-773895\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/FormatRules-GoogleSheetsConditionalFormattingDate.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose the rules for the date\" width=\"421\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll then see a default formatting style display at the bottom. But you can, of course, customize the formatting to fit your needs. You can use bold, italic, or underline, and you can apply a color to the font. You can also pick a fill color for the cell(s). If you want to apply more than one style, you can do this as well.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-773896\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/Format-GoogleSheetsConditionalFormattingDate.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose the format for the cells\" width=\"293\" height=\"321\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>When you finish, click \u201cDone.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-773897\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/Formatted-GoogleSheetsConditionalFormattingDate.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Conditional formatting applied in Google Sheets\" width=\"680\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Edit_Delete_or_Add_Another_Rule_in_Google_Sheets\"><\/span>Edit, Delete, or Add Another Rule in Google Sheets<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You can edit or delete an existing conditional formatting rule or set up another. Reopen the sidebar by clicking Format &gt; Conditional Formatting.<\/p>\n<ul>\n<li>To edit a rule, simply select it, make your changes, and click \u201cDone.\u201d<\/li>\n<li>To delete a rule, click the trash can icon on the right.<\/li>\n<li>To set up another formatting rule in the same sheet or for the same set of cells, select \u201cAdd Another Rule\u201d and follow the process above.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-773898\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/12\/EditDeleteAdd-GoogleSheetsConditionalFormattingDate.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Edit, delete, or add another rule\" width=\"300\" height=\"187\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Highlighting important dates automatically is an excellent use for conditional formatting. For another, look at how you can apply a color scale based on values in Google Sheets.<\/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\/773887\/how-to-use-conditional-formatting-based-on-date-in-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Use Conditional Formatting Based on Date in Google Sheets&#8221; With conditional formatting, you can highlight data on your spreadsheet automatically. If you have a sheet where you need the dates easier to spot, you can use conditional formatting in Google Sheets based on date. You might have a spreadsheet containing due dates for&#8230;<\/p>\n","protected":false},"author":1,"featured_media":388709,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-388708","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\/388708","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=388708"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/388708\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/388709"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=388708"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=388708"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=388708"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}