PowerPivot, time between two times, one date cell

[TheComputerMan08]

Quick question for everyone who might be able to help:

I work in a call center and I am retrieving data from the call database, however, we have a need to show things broken down into half hour intervals. I see a LOT of tutorials about how to find the difference between two cells of time, but I only have one.

Basically, I have a datetime field, and I want to do this:

if the time falls between 00:00:00 and 00:29:00

I want the interval field I have to show nothing but "0"

then if it's 00:30:00 through 00:59:00

I want it to show "30"

then "100"
then "130"

etc.

but I can't do any more nested if's than 7, and vlookup is obnoxious to powerpivot for 86,000 calls and counting.
I am also having trouble getting JUST the time values to even compare it in the first place.


How can I do something like this?

I had a VB app doing this for me, and it was a bunch of elseif's, but with excel it's not as easy as manipulating data with code.

Any suggestions help.

thanks!

Brent

 

[TheComputerMan08]

Thought just crossed my mind, would I be better off to try and create a field of my own using the SQL query I have getting the data I've already got? I haven't researched about doing that yet, but maybe that's a better way to go to at least get the time field by itself first, and still then find a way to compare the times?

Just thinking out loud.