Group by half-hour increments

[jkupov]

I usually group one of my reports by hour increments by using the hour function (each record in the DB has a time stamp for the service call received time) to determine the number of service items per hour per day of week. However, my boss now wants this done by half hour increments. Is there a way to take a date/time field and display the hour:half-hour for that field? In other words, instead of this:

10:00 # of service calls
11:00 # of service calls
12:00 # of service calls

He now wants to see this"

10:00 # of service calls
10:30 # of service calls
11:00 # of service calls
11:30 # of service calls

etc.

The field that I will be running this on is actually the time that the service call came into the call center. Any ideas? Thanks.

 

[jkupov]

Sorry, I forgot to mention that this data is imported into excel so that I can use pivot tables on the data. SO I will need an excel formula since a function does not exist for the hour:half-hour

 

[mintjulep]

How about MINUTE()<30 and MINUTE ()>= 30

 

[SkipVought]

Construct your own summary.

1. My Source Data using Named Ranges.
[tt]
tim amt
1:00 1
1:30 2
2:00 3
2:30 4
1:15 5
2:25 6
[/tt]
2. My Base Values
[tt]
Tims
1:00
1:30
2:00
2:30
3:00
[/tt]
Second value formula copied down
[tt]
D3: =D2+TIME(0,30,0)
[/tt]
3. TOT count, which is a SUM on Amt...
[tt
F2: =SUMPRODUCT((tim>=D2)*(tim<D3)*(amt))[/v]
[/tt]
MY RESULTS
[tt]
Tims Tot
1:00 6
1:30 2
2:00 9
2:30 4
3:00
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}