Excel can organize data by half hour blocks...I just don't know how to do it.

O

obyone

New Member
Joined
Jul 13, 2010
Messages
6
I know I'm new here so I tried a search. Either it hasn't been crossed yet or I suck at searching. :(

I was give a report with 2 years worth of daily data. The Time/Date field is listed in the following format: 2009/06/09 14:25:47 I need to find a way to have excel organize these in half hour increments.

My end goal is a final report breaking down each day into half hour increments and showing how many inquiries came in during that half hour block.

Example (pic below) of desired final output:
on 2009/06/11, 6 inquires were handled, the end report (currently being counted in a separate spreadsheet) would show

1 for 3:00-3:30,
1 for 9:00-9:30,
1 for 11:00-11:30,
2 for 2:00-2:30 &
1 for 6:00-6:30.

like so. Note, the times written were provided by someone else. If everything has to be by 24 hr clock, I have no problem changing that.
ScreenShot.jpg


Right now I'm counting these by hand but that's going to take forever. I have till the end of the week. :(
 
Sort by date Sort by votes
Have you tried using a pivot table? You can group the dates by Month, Day, and Hour increments...etc

Are you familiar with Pivot Tables?
 
Upvote 0
You need to split the dates out into a column and split the 1/2 hour increments into a column

If your Date/Time values are in column H (starting at H15), put this in I15...
=INT(H15)
...and copy it down column I for each Date/Time in column H. This will give you the Date only with no Time values. You can format column I with any date format you like.

In J15 put this...
=MOD(INT(H15/(1/48))*(1/48),1)
...and copy it down column J for each Date/Time in column H. This will group each time into half hour intervals.

Now you could select your data and make a Pivot Table where the Dates are the columns, Time intervals are the Rows, and Count of half hour time intervals is the data.


<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:144px;" /><col style="width:61.6px;" /><col style="width:128px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-weight:bold; text-align:center; ">Date-Time</td><td style="font-weight:bold; text-align:center; ">Date</td><td style="font-weight:bold; text-align:center; ">1/2 Hour</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:center; ">7/13/10 7:50 PM</td><td style="text-align:center; ">7/13/10</td><td style="text-align:center; ">7:30 PM</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:center; ">7/13/10 7:58 PM</td><td style="text-align:center; ">7/13/10</td><td style="text-align:center; ">7:30 PM</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:center; ">7/13/10 8:06 PM</td><td style="text-align:center; ">7/13/10</td><td style="text-align:center; ">8:00 PM</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:center; ">7/13/10 8:14 PM</td><td style="text-align:center; ">7/13/10</td><td style="text-align:center; ">8:00 PM</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:center; ">7/13/10 8:22 PM</td><td style="text-align:center; ">7/13/10</td><td style="text-align:center; ">8:00 PM</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:center; ">7/13/10 8:30 PM</td><td style="text-align:center; ">7/13/10</td><td style="text-align:center; ">8:30 PM</td></tr><tr style="height:22px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:center; ">7/13/10 8:38 PM</td><td style="text-align:center; ">7/13/10</td><td style="text-align:center; ">8:30 PM</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I15</td><td >=INT(H15)</td></tr><tr><td >J15</td><td >=MOD(INT<span style=' color:008000; '>(H15/<span style=' color:#0000ff; '>(1/48)</span>)</span>*<span style=' color:008000; '>(1/48)</span>,1)</td></tr><tr><td >I16</td><td >=INT(H16)</td></tr><tr><td >J16</td><td >=MOD(INT<span style=' color:008000; '>(H16/<span style=' color:#0000ff; '>(1/48)</span>)</span>*<span style=' color:008000; '>(1/48)</span>,1)</td></tr><tr><td >I17</td><td >=INT(H17)</td></tr><tr><td >J17</td><td >=MOD(INT<span style=' color:008000; '>(H17/<span style=' color:#0000ff; '>(1/48)</span>)</span>*<span style=' color:008000; '>(1/48)</span>,1)</td></tr><tr><td >I18</td><td >=INT(H18)</td></tr><tr><td >J18</td><td >=MOD(INT<span style=' color:008000; '>(H18/<span style=' color:#0000ff; '>(1/48)</span>)</span>*<span style=' color:008000; '>(1/48)</span>,1)</td></tr><tr><td >I19</td><td >=INT(H19)</td></tr><tr><td >J19</td><td >=MOD(INT<span style=' color:008000; '>(H19/<span style=' color:#0000ff; '>(1/48)</span>)</span>*<span style=' color:008000; '>(1/48)</span>,1)</td></tr><tr><td >I20</td><td >=INT(H20)</td></tr><tr><td >J20</td><td >=MOD(INT<span style=' color:008000; '>(H20/<span style=' color:#0000ff; '>(1/48)</span>)</span>*<span style=' color:008000; '>(1/48)</span>,1)</td></tr><tr><td >I21</td><td >=INT(H21)</td></tr><tr><td >J21</td><td >=MOD(INT<span style=' color:008000; '>(H21/<span style=' color:#0000ff; '>(1/48)</span>)</span>*<span style=' color:008000; '>(1/48)</span>,1)</td></tr></table></td></tr></table>
 
Upvote 0
AlphaFrog said:
You need to split the dates out into a column and split the 1/2 hour increments into a column

If your Date/Time values are in column H (starting at H15), put this in I15...
=INT(H15)
...and copy it down column I for each Date/Time in column H. This will give you the Date only with no Time values. You can format column I with any date format you like.

In J15 put this...
=MOD(INT(H15/(1/48))*(1/48),1)
...and copy it down column J for each Date/Time in column H. This will group each time into half hour intervals.

Now you could select your data and make a Pivot Table where the Dates are the columns, Time intervals are the Rows, and Count of half hour time intervals is the data.


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 144px"><COL style="WIDTH: 61px"><COL style="WIDTH: 128px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD>*</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Date-Time</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/2 Hour</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center">7/13/10 7:50 PM</TD><TD style="TEXT-ALIGN: center">7/13/10</TD><TD style="TEXT-ALIGN: center">7:30 PM</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center">7/13/10 7:58 PM</TD><TD style="TEXT-ALIGN: center">7/13/10</TD><TD style="TEXT-ALIGN: center">7:30 PM</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: center">7/13/10 8:06 PM</TD><TD style="TEXT-ALIGN: center">7/13/10</TD><TD style="TEXT-ALIGN: center">8:00 PM</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: center">7/13/10 8:14 PM</TD><TD style="TEXT-ALIGN: center">7/13/10</TD><TD style="TEXT-ALIGN: center">8:00 PM</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: center">7/13/10 8:22 PM</TD><TD style="TEXT-ALIGN: center">7/13/10</TD><TD style="TEXT-ALIGN: center">8:00 PM</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: center">7/13/10 8:30 PM</TD><TD style="TEXT-ALIGN: center">7/13/10</TD><TD style="TEXT-ALIGN: center">8:30 PM</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center">7/13/10 8:38 PM</TD><TD style="TEXT-ALIGN: center">7/13/10</TD><TD style="TEXT-ALIGN: center">8:30 PM</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I15</TD><TD>=INT(H15)</TD></TR><TR><TD>J15</TD><TD>=MOD(INT(H15/(1/48))*(1/48),1)</TD></TR><TR><TD>I16</TD><TD>=INT(H16)</TD></TR><TR><TD>J16</TD><TD>=MOD(INT(H16/(1/48))*(1/48),1)</TD></TR><TR><TD>I17</TD><TD>=INT(H17)</TD></TR><TR><TD>J17</TD><TD>=MOD(INT(H17/(1/48))*(1/48),1)</TD></TR><TR><TD>I18</TD><TD>=INT(H18)</TD></TR><TR><TD>J18</TD><TD>=MOD(INT(H18/(1/48))*(1/48),1)</TD></TR><TR><TD>I19</TD><TD>=INT(H19)</TD></TR><TR><TD>J19</TD><TD>=MOD(INT(H19/(1/48))*(1/48),1)</TD></TR><TR><TD>I20</TD><TD>=INT(H20)</TD></TR><TR><TD>J20</TD><TD>=MOD(INT(H20/(1/48))*(1/48),1)</TD></TR><TR><TD>I21</TD><TD>=INT(H21)</TD></TR><TR><TD>J21</TD><TD>=MOD(INT(H21/(1/48))*(1/48),1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Click to expand...

First off, the skill of your internetz is amazing!
eek.gif


Next, I know you said to copy down the formulas for every date afterwards...I hope I'm just not understanding because that would mean I'd have to manually change the cell number for each formula that I copy. That's 2 years worth of cells.
eek.gif


Also, the date formula (to strip the time) copied the date but placed the timestamp as 00:00:00 and column J came out to a decimal. 0.020833333

I know the problem is between the chair and the keyboard so I may need a bit more hand holding. :)
 
Upvote 0
Put the 1st formula in I15...
=INT(H15)
...and enter.

Click on cell I15 so there is a black outline around it
Notice the small black square in the lower right of the outline. That square is called the Autofill Handle. Grab it with the mouse and drag it down the column. This will automatically fill in the formulas down the column. The cell references will also automatically change e.g. H15 will change to H16, H17, H18 ...etc.

Put the formula in J15
Right-click on J15 and select Format Cells from the pop-up menu
On the "Number" tab, select a "Time" format you like and OK
Autofill the formulas down column J.

As far as making a Pivot table. Do a web search for "Excel Pivot Table Video" and watch a few. See can figure it out from there. If not, come back and ask a specific Pivot Table question.
 
Upvote 0
It worked! And this is how I felt after seeing how easily it did work.

facepalming.gif


lol

You guys are amazing! You may have these, as a token of my appreciation. Enjoy

1264907015583.gif


2k22dk.gif


EcGYq.gif
 
Upvote 0
AlphaFrog said:
You need to split the dates out into a column and split the 1/2 hour increments into a column

If your Date/Time values are in column H (starting at H15), put this in I15...
=INT(H15)
...and copy it down column I for each Date/Time in column H. This will give you the Date only with no Time values. You can format column I with any date format you like.

In J15 put this...
=MOD(INT(H15/(1/48))*(1/48),1)
...and copy it down column J for each Date/Time in column H. This will group each time into half hour intervals.

Now you could select your data and make a Pivot Table where the Dates are the columns, Time intervals are the Rows, and Count of half hour time intervals is the data.


*HIJ

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:144px;"><col style="width:61.6px;"><col style="width:128px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: center"]Date-Time[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]1/2 Hour[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: center"]7/13/10 7:50 PM[/TD]
[TD="align: center"]7/13/10[/TD]
[TD="align: center"]7:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]7/13/10 7:58 PM[/TD]
[TD="align: center"]7/13/10[/TD]
[TD="align: center"]7:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: center"]7/13/10 8:06 PM[/TD]
[TD="align: center"]7/13/10[/TD]
[TD="align: center"]8:00 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: center"]7/13/10 8:14 PM[/TD]
[TD="align: center"]7/13/10[/TD]
[TD="align: center"]8:00 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: center"]7/13/10 8:22 PM[/TD]
[TD="align: center"]7/13/10[/TD]
[TD="align: center"]8:00 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: center"]7/13/10 8:30 PM[/TD]
[TD="align: center"]7/13/10[/TD]
[TD="align: center"]8:30 PM[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: center"]7/13/10 8:38 PM[/TD]
[TD="align: center"]7/13/10[/TD]
[TD="align: center"]8:30 PM[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
I15=INT(H15)
J15=MOD(INT(H15/(1/48))*(1/48),1)
I16=INT(H16)
J16=MOD(INT(H16/(1/48))*(1/48),1)
I17=INT(H17)
J17=MOD(INT(H17/(1/48))*(1/48),1)
I18=INT(H18)
J18=MOD(INT(H18/(1/48))*(1/48),1)
I19=INT(H19)
J19=MOD(INT(H19/(1/48))*(1/48),1)
I20=INT(H20)
J20=MOD(INT(H20/(1/48))*(1/48),1)
I21=INT(H21)
J21=MOD(INT(H21/(1/48))*(1/48),1)

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...


You saved my life. &#55357;&#56908;&#55358;&#56607;
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
300
CaptainGravyBum
C
A
  • Solved
How do I increase efficiency or stop the lag?
Replies
3
Views
67
Aphten
A
W
  • Solved
Convert hourly value into two half hourly values
Replies
5
Views
2K
Fluff
Fluff
A
  • Question Question
Excel is getting slower as I add more sheets
Replies
3
Views
100
Micron
M
esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
809
esset
esset

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top