Average Difference Between Two Dates, excluding certain hours.

[iherndon]

Each month I have a report that has a ticket Start and Stop Date/Time.

The goal is to determine performance adherence for tickets that were submitted during hours of operation (9am-6pm) Monday - Friday

The date format is as follows:

start stop
4/3/2011 7:15 4/4/2011 10:25
4/3/2011 18:45 4/4/2011 10:25
4/3/2011 20:35 4/4/2011 10:20
4/3/2011 20:45 4/4/2011 10:20
4/3/2011 23:05 4/4/2011 10:15
4/3/2011 23:30 4/4/2011 10:05
4/4/2011 3:05 4/4/2011 11:00
4/4/2011 3:55 4/4/2011 10:00
4/4/2011 10:40 4/4/2011 10:55
4/4/2011 12:55 4/4/2011 13:05
4/4/2011 20:30 4/5/2011 10:15
4/4/2011 20:30 4/5/2011 9:45
4/4/2011 23:45 4/5/2011 17:50
4/5/2011 0:00 4/5/2011 9:40
4/5/2011 0:25 4/5/2011 9:40
4/5/2011 1:05 4/5/2011 9:40

So basically with the above info I'm needing to omit anything in the Start column that was outside hours of operation (9a-6p)

Thanks!

 

[iliace]

Are both date/time values in the same cell? In other words, does it look like this?

Excel Workbook
	A	B	C	D
1	4/3/2011	7:15	4/4/2011	10:25
2	4/3/2011	18:45	4/4/2011	10:25
3	4/3/2011	20:35	4/4/2011	10:20
4	4/3/2011	20:45	4/4/2011	10:20
Sheet1

or like this?

Excel Workbook
	A	B
18	4/3/2011 7:15	4/4/2011 10:25
19	4/3/2011 18:45	4/4/2011 10:25
20	4/3/2011 20:35	4/4/2011 10:20
21	4/3/2011 20:45	4/4/2011 10:20
Sheet1

or like this?

Excel Workbook
	A
36	4/3/2011 7:15 4/4/2011 10:25
37	4/3/2011 18:45 4/4/2011 10:25
38	4/3/2011 20:35 4/4/2011 10:20
39	4/3/2011 20:45 4/4/2011 10:20
Sheet1

 

[iherndon]

Sorry I didn't specify that. They are two columns like your second example.

 

[iliace]

Well, here's two versions. The one in D2 ignores any submission made outside of the 9am-6pm timeframe completely - note, it will also ignore anything submitted exactly at 6pm. The one in D3 takes any outside submissions and rounds them up to the next business morning (9am), however it does not take into account weekends.

If you have Excel 2007 or later, you can achieve the first result (D2) simply by using the AVERAGEIFS() function without the array formula.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:142px;" /><col style="width:150px;" /><col style="width:33px;" /><col style="width:58px;" /><col style="width:259px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; ">Start</td><td style="font-weight:bold; text-align:center; ">Stop</td><td >*</td><td style="font-weight:bold; text-align:center; ">Average</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">4/3/2011 7:15</td><td style="text-align:right; ">4/4/2011 10:25</td><td >*</td><td style="text-align:right; ">0:12</td><td ><--- not counting early/late submissions</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">4/3/2011 18:45</td><td style="text-align:right; ">4/4/2011 10:25</td><td >*</td><td style="text-align:right; ">3:53</td><td ><--- rounding early/late submissions</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4/3/2011 20:35</td><td style="text-align:right; ">4/4/2011 10:20</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4/3/2011 20:45</td><td style="text-align:right; ">4/4/2011 10:20</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">4/3/2011 23:05</td><td style="text-align:right; ">4/4/2011 10:15</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">4/3/2011 23:30</td><td style="text-align:right; ">4/4/2011 10:05</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">4/4/2011 3:05</td><td style="text-align:right; ">4/4/2011 11:00</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4/4/2011 3:55</td><td style="text-align:right; ">4/4/2011 10:00</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">4/4/2011 10:40</td><td style="text-align:right; ">4/4/2011 10:55</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">4/4/2011 12:55</td><td style="text-align:right; ">4/4/2011 13:05</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">4/4/2011 20:30</td><td style="text-align:right; ">4/5/2011 10:15</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">4/4/2011 20:30</td><td style="text-align:right; ">4/5/2011 9:45</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">4/4/2011 23:45</td><td style="text-align:right; ">4/5/2011 17:50</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">4/5/2011 0:00</td><td style="text-align:right; ">4/5/2011 9:40</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">4/5/2011 0:25</td><td style="text-align:right; ">4/5/2011 9:40</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">4/5/2011 1:05</td><td style="text-align:right; ">4/5/2011 9:40</td><td >*</td><td >*</td><td >*</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 >D2</td><td >{=AVERAGE(IF<span style=' color:008000; '>(<span style=' color:#0000ff; '>(HOUR<span style=' color:#ff0000; '>($A$2:$A$17)</span>>=9)</span>*<span style=' color:#0000ff; '>(HOUR<span style=' color:#ff0000; '>($A$2:$A$17)</span><18)</span>, $B$2:$B$17-$A$2:$A$17)</span>)}</td></tr><tr><td >D3</td><td >{=AVERAGE($B$2:$B$17-IF<span style=' color:008000; '>(HOUR<span style=' color:#0000ff; '>(--$A$2:$A$17)</span><9, INT<span style=' color:#0000ff; '>(--$A$2:$A$17)</span>+<span style=' color:#0000ff; '>(9/24)</span>, IF<span style=' color:#0000ff; '>(HOUR<span style=' color:#ff0000; '>(--$A$2:$A$17)</span>>18, ROUND<span style=' color:#ff0000; '>(--$A$2:$A$17, 0)</span>+<span style=' color:#ff0000; '>(9/24)</span>, --$A$2:$A$17)</span>)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

 

[iherndon]

ah that's perfect! Not since my original post the Hours have actually changed to 9am to 10pm, 7 days a week. Does that mess with the formula at all? Is it something simple for pretty much anyone to modify the finished formulate and input new 'staffed hour' ranges by inputting the real figures, or is it some type of excel conversion that needs to be entered? I ask bc I know dates and whatnot are actually figured in terms of things like 0.123325 etc.

 

[iliace]

Anywhere you see this:

>=9
>=18

you're checking to make sure the hour is after 9am or after 6pm, respectively. This:

<9
<18


checks to make sure the hour is before 9am or before 6pm, respectively. So, if you change it to <22 instead, you will be OK. Also, you see this in the second formula:

+(9/24)

That works for 9am. So, if your Start hour changed to 8am, you'd adjust that to be +(8/24) respectively. You can of course simplify the fraction, but that makes future editing trickier.

***** note there was an error in the original second formula, which counted submissions during the 6pm hour - corrected below *****

I would recommend putting those two values in separate cells, and then address them, so you only have to change the formula in one place. I modified the original formulas for you as an example (moving everything over one column):


<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84px;" /><col style="width:49px;" /><col style="width:58px;" /><col style="width:322px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">*</td><td >*</td><td style="font-weight:bold; text-align:center; ">Average</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Earliest start</td><td style="text-align:right; ">9</td><td style="text-align:right; ">0:12</td><td ><--- not counting early/late submissions</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Latest stop</td><td style="text-align:right; ">18</td><td style="text-align:right; ">3:00</td><td ><--- rounding early/late submissions</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 >E2</td><td >{=AVERAGE(IF<span style=' color:008000; '>(<span style=' color:#0000ff; '>(HOUR<span style=' color:#ff0000; '>($A$2:$A$17)</span>>=$D$2)</span>*<span style=' color:#0000ff; '>(HOUR<span style=' color:#ff0000; '>($A$2:$A$17)</span><$D$3)</span>, $B$2:$B$17-$A$2:$A$17)</span>)}</td></tr><tr><td >E3</td><td >{=AVERAGE($B$2:$B$17-IF<span style=' color:008000; '>(HOUR<span style=' color:#0000ff; '>(--$A$2:$A$17)</span><9, INT<span style=' color:#0000ff; '>(--$A$2:$A$17)</span>+<span style=' color:#0000ff; '>(9/24)</span>, IF<span style=' color:#0000ff; '>(HOUR<span style=' color:#ff0000; '>(--$A$2:$A$17)</span>>=$D$3, ROUND<span style=' color:#ff0000; '>(--$A$2:$A$17, 0)</span>+<span style=' color:#ff0000; '>(9/24)</span>, --$A$2:$A$17)</span>)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

 

[iherndon]

Yah good thinking. Actually that's super simple with it just being 9 or 18 like that. I'm going to try to figure out how to make a simple section for 'hours to average' where it's pretty much fields like below

Average Response Time

Choose Staff Hours

Start Time ___
Stop Time ___

Days of Week
Y/N Monday
Y/N Tuesday
Y/N Wednesday
Y/N Thursday
Y/N Friday
Y/N Saturday
Y/N Sunday

Then with the above, simply entering the information in that criteria area you could very easily figure out ART based on just specific days as well for a certain period. That should be interesting for me to figure out... lol. Thanks again for the help with those formulas and all the details you provided!! Going to try that in a few hours when I have the chance. Will let you know how it goes.

 

[iliace]

Great! Post back with your solutions, and/or if you get stuck.