{"id":5218,"date":"2020-12-03T15:41:28","date_gmt":"2020-12-03T14:41:28","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=5218"},"modified":"2024-02-06T16:00:31","modified_gmt":"2024-02-06T15:00:31","slug":"group-by-time-with-excel-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-by-time-with-excel-pivot-tables\/","title":{"rendered":"2 Quick Ways to Group Time in Excel Pivot Tables"},"content":{"rendered":"<p><em>Want to know how to Excel <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Table\" data-wpil-keyword-link=\"linked\">Pivot Table<\/a> group by 15 minutes, 1 hour, or a custom time interval? Keep Reading!<\/em><\/p>\n<p>Let&#8217;s put yourself in my place for a second.<\/p>\n<p>It was a dark, rainy night in Dublin city and I was working overtime&#8230;again!<\/p>\n<p>I downloaded a report with lots of transnational data. \u00a0<em><strong>Time of Sale<\/strong><\/em> in one column and <em><strong>Sales<\/strong> <\/em>in another, among other columns.<\/p>\n<p>I wanted to get the Sales numbers by the hour so that I could understand which <strong>part of the day had the best sales and which is the slowest time of the day<\/strong>.<\/p>\n<p>Grouping these would take a ton of effort! Thankfully there is the Pivot Table way, which is quick and reduces the risks of making any errors&#8230;.and it makes updating the report easy with any new additional data!<\/p>\n<p>&nbsp;<\/p>\n<p>In this article, you will be provided a detailed guide on the following:<\/p>\n<ul>\n<li><a href=\"#group-by-hour\"><strong>Group by Hour<\/strong><\/a><\/li>\n<li><a href=\"#group-by-custom-time-interval\"><strong>Group by Custom Time Interval<\/strong><\/a><\/li>\n<\/ul>\n<p>Let&#8217;s look at each one of these methods on How to group time intervals in Excel using a real-life example!<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><a id=\"group-by-hour\"><\/a>Group by Hour<\/strong><\/h3>\n<p>In the example below, you have Sales data and you want to get the Sales Grouped by Time.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-697.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone wp-image-19620 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-697.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"945\" height=\"473\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-697.png 945w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-697-300x150.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-697-768x384.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Watch it on <a href=\"https:\/\/youtu.be\/iuIwRUkGWSA\" target=\"_blank\" rel=\"nofollow noopener\"><strong>YouTube<\/strong><\/a> and give it a thumbs-up!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/iuIwRUkGWSA?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p>Follow the <strong>step-by-step tutorial<\/strong> below on How to group time in Excel and make sure to <strong>download the Excel Workbook<\/strong> to follow along:<\/p>\n<p><strong><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/09\/Group-by-Time.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Group-by-Time.xlsx<\/span><\/a><\/strong><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong>\u00a0<\/span>Insert a new Pivot table by clicking\u00a0on your data and going\u00a0to<strong><em>\u00a0Insert &gt; Pivot Table &gt; New Worksheet or Existing Worksheet<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5220\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-01.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"386\" height=\"344\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-01.png 386w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-01-300x267.png 300w\" sizes=\"(max-width: 386px) 100vw, 386px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span>\u00a0In the\u00a0<strong>ROWS<\/strong>\u00a0section put in the <em><strong>Time of Sale<\/strong>\u00a0<\/em>field.<\/p>\n<p>Notice Excel will automatically Group the <strong><em>Time of Sale<\/em><\/strong>\u00a0field in Excel 2016 (a new feature)<strong>:<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5221\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-02.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"354\" height=\"637\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-02.png 354w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-02-167x300.png 167w\" sizes=\"(max-width: 354px) 100vw, 354px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong>\u00a0<\/span>Right-click on any Row item in your Pivot Table and select<strong>\u00a0Group<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5222\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-03.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"347\" height=\"596\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-03.png 347w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-03-175x300.png 175w\" sizes=\"(max-width: 347px) 100vw, 347px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span><strong>\u00a0<\/strong>It was able to determine our time range (minimum time and maximum time).<\/p>\n<p>Make sure only <strong>Hours<\/strong><b>\u00a0<\/b>is\u00a0selected (blue highlight).<\/p>\n<p>To create Excel Pivot Table group time by hour,\u00a0 Click\u00a0<strong>OK.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5223\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-04.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"235\" height=\"307\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-04.png 235w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-04-230x300.png 230w\" sizes=\"(max-width: 235px) 100vw, 235px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span> In the\u00a0<strong>VALUES <\/strong>area\u00a0put in the\u00a0<em>Sales\u00a0<\/em>field. This will get the total of the Sales for each hourly\u00a0range you have defined:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5224\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-05.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"354\" height=\"637\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-05.png 354w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-05-167x300.png 167w\" sizes=\"(max-width: 354px) 100vw, 354px\" \/><\/a><\/p>\n<p>Now we have our Excel Pivot Table group time by hour, notice that we can improve the formatting:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-05b.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone wp-image-5230 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-05b.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"162\" height=\"279\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6:\u00a0<\/strong><\/span>Click the <strong>Sum of SALES<\/strong> and select\u00a0<strong>Value Field Settings<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5225\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-06.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"377\" height=\"641\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-06.png 377w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-06-176x300.png 176w\" sizes=\"(max-width: 377px) 100vw, 377px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7:\u00a0<\/strong><\/span>Select\u00a0<strong>Number Format<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5226\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-07.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"373\" height=\"319\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-07.png 373w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-07-300x257.png 300w\" sizes=\"(max-width: 373px) 100vw, 373px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 8:\u00a0<\/strong><\/span>Select\u00a0<strong>Currency.\u00a0<\/strong>Click\u00a0<strong>OK.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-08.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5227\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-08.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"529\" height=\"465\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-08.png 529w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-08-300x264.png 300w\" sizes=\"(max-width: 529px) 100vw, 529px\" \/><\/a><\/p>\n<p>You\u00a0now have your total sales for each hourly period!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-09.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-5228\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Group-by-Time-09.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"173\" height=\"289\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>Extra Tip:<\/strong><\/span>\u00a0You can Right Click on any <em>Sales<\/em> value in the Pivot Table and select <strong>Sort &gt; Largest to Smallest<\/strong>. \u00a0This will show you the\u00a0part of the day that has the most sales at the top!<\/p>\n<p>The steps mentioned above can be used to Excel Pivot Table group time by hour.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><a id=\"group-by-custom-time-interval\"><\/a>Group by Custom Time Interval<\/strong><\/h3>\n<p>There might be a time when you want to review the<strong> data based on a specific time block instead of a standard 1-hour<\/strong> interval. Like, you want the data to <strong>Excel Pivot Table group by 15 minutes or 2 hours<\/strong> or any other custom time interval.<\/p>\n<p>You can easily do that using Excel&#8217;s <strong>FLOOR<\/strong> function.<\/p>\n<h4>Before we learn how to Excel Pivot Table group by 15 minutes, let&#8217;s first understand the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/10-1-ways-to-round-numbers-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>FLOOR function<\/strong><\/a> and its use!<\/h4>\n<p>&nbsp;<\/p>\n<p>FLOOR Function in Excel can be <strong>used to round a number down to the nearest multiple of significance<\/strong>.<\/p>\n<p><em><strong>The Syntax of the FLOOR function is :<\/strong><\/em><\/p>\n<p><strong><span style=\"color: #ff0000;\">=FLOOR (number, significance)<\/span><\/strong><\/p>\n<p>where<\/p>\n<ul>\n<li><strong>number<\/strong> &#8211; The numeric value you want to round<\/li>\n<li><strong>significance<\/strong> &#8211; The multiple to which you want to round.<\/li>\n<\/ul>\n<p>Luckily, <strong>Excel treats date and time as numbers.\u00a0<\/strong>The <strong>integer portion of the number represents the day<\/strong> and the <strong>decimal portion represents the time<\/strong>.<\/p>\n<p>The FLOOR function also <strong>accepts an argument in &#8220;hh: mm&#8221; format<\/strong> and it easily converts them to decimal values. For example, Excel converts 00:15 into 0.0104166666666667, which is the decimal value of 15 minutes, and rounds using that value.<\/p>\n<p>&nbsp;<\/p>\n<p>Now, let&#8217;s take the same example and create an Excel Pivot Table group by 15 minutes.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong>\u00a0<\/span>Insert a new column named <strong>FLOOR<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-702.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19668\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-702.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"1317\" height=\"452\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-702.png 1317w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-702-300x103.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-702-1024x351.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-702-768x264.png 768w\" sizes=\"(max-width: 1317px) 100vw, 1317px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong>\u00a0<\/span>Insert the function FLOOR.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>=FLOOR (<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-704.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19669\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-704.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"683\" height=\"473\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-704.png 683w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-704-300x208.png 300w\" sizes=\"(max-width: 683px) 100vw, 683px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong>\u00a0<\/span>Insert the first argument &#8211; Time of Sales.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>=FLOOR ([@TIME OF SALES]<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-705.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19670\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-705.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"768\" height=\"471\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-705.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-705-300x184.png 300w\" sizes=\"(max-width: 768px) 100vw, 768px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong>\u00a0<\/span>Insert the second argument &#8211; significance. Here, it is 15 minutes ie 00:15.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>=FLOOR ([@TIME OF SALES],&#8221;00:15&#8243;)<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-706.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19671\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-706.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"767\" height=\"495\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-706.png 767w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-706-300x194.png 300w\" sizes=\"(max-width: 767px) 100vw, 767px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span> Copy-paste the <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a> down.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-708.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19672\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-708.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"697\" height=\"495\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-708.png 697w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-708-300x213.png 300w\" sizes=\"(max-width: 697px) 100vw, 697px\" \/><\/a><\/p>\n<p>As you can the FLOOR function has rounded the time of sales in intervals of 15 minutes.<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6:<\/strong><\/span> In the PivotTable Fields, drag and drop <strong>Time of Sales under Row<\/strong> column and <strong>Sales under Values<\/strong> column.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-709.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19673\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-709.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"261\" height=\"522\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-709.png 261w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-709-150x300.png 150w\" sizes=\"(max-width: 261px) 100vw, 261px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 7:<\/span> <\/strong>The columns will be displayed in the PivotTable.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-710.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19674\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-710.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"334\" height=\"449\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-710.png 334w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-710-223x300.png 223w\" sizes=\"(max-width: 334px) 100vw, 334px\" \/><\/a><\/p>\n<p>This is how you can Excel Pivot Table group by 15 minutes!<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>Pro-Tip:<\/strong><\/span> In case, you have <strong>data containing date and time together, you can easily extract time<\/strong> from that column and then perform the group feature.<\/p>\n<p>&nbsp;<\/p>\n<p>Before we move forward, let&#8217;s first understand how to remove decimal from a number in Excel. <strong>TRUNC function<\/strong> can be used to truncate a number by <strong>removing the decimal portion of a number<\/strong>.<\/p>\n<p>It <strong>does not round off<\/strong> the number, it simply truncates the number.<\/p>\n<p>For example, TRUNC(11.8) will return 11, and TRUNC(-9.2) will return -9.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/09\/Screenshot-983.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-21905\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/09\/Screenshot-983.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"376\" height=\"303\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/09\/Screenshot-983.png 376w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/09\/Screenshot-983-300x242.png 300w\" sizes=\"(max-width: 376px) 100vw, 376px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><em>Going back to the previous example!\u00a0 <\/em><\/p>\n<p>You can subtract the Date with time from the truncated date and will r<strong>eturn only the decimal number<\/strong>. This will extract the time of sales.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-712.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"2 Quick Ways to Group Time in Excel Pivot Tables\"  class=\"alignnone size-full wp-image-19675\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-712.png\" alt=\"2 Quick Ways to Group Time in Excel Pivot Tables\" width=\"930\" height=\"491\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-712.png 930w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-712-300x158.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-712-768x405.png 768w\" sizes=\"(max-width: 930px) 100vw, 930px\" \/><\/a><\/p>\n<p>This is how you can Pivot Table group by time range like every half hour \u201c0:30\u201d or every five minutes \u201c0:05\u201d.<\/p>\n<p>&nbsp;<\/p>\n<h3>Conclusion<\/h3>\n<p>If you wish you<strong> group time by 1-hour interval<\/strong>, you can simply use the group function available in Pivot Table.<\/p>\n<p>But if you <strong>want to add a custom time interval<\/strong> like Excel pivot table group by 15 minutes, you can use the floor function to round off the time to a custom interval.<\/p>\n<p><strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-dates-with-a-pivot-table\/\" target=\"_blank\">Click here<\/a><\/strong>\u00a0to learn how to Group Dates in Pivot Table!<\/p>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-by-sales-range\/\" target=\"_blank\" rel=\"noopener\">Group by Sales Range in Excel Pivot Tables<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-two-pivot-tables-independently\/\" target=\"_blank\" rel=\"noopener\">Group Two Excel Pivot Tables Independently<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-by-text-fields-in-pivot-table\/\" target=\"_blank\" rel=\"noopener\">Group by Text fields in Excel Pivot Tables<\/a><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100.html\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2964 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/728x90.gif\" alt=\"PIVOT BANNER\" width=\"728\" height=\"90\" title=\"\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Want to know how to Excel Pivot Table group by 15 minutes, 1 hour, or a custom time interval? Keep Reading! Let&#8217;s put yourself in my place for a second. It was a dark, rainy night in Dublin city and I was working overtime&#8230;again! I downloaded a report with lots of transnational data. \u00a0Time of [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17212,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[4,19],"tags":[132,139,207],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5218"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=5218"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5218\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17212"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=5218"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=5218"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=5218"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}