Trying to create an hourly report

[PlaidEmp]

I belive the error is in the formula or myself.

The formula I made is supposed to take the calls that are logged into our tracking program and give me an hourly total. Currently it is getting confused by the AM PM and the Tracking software(Track-It!) doesn't seem to allow 24hour times.

Here is what is in the formula:

StringVar Array Otime:= ["1:00","2:00","3:00","4:00","5:00","6:00","7:00","8:00","9:00","10:00","11:00","12:00"];
StringVar Array Omeridian:= ["a","p"];


if {TASKS.REQTIME} > '12:59p' and {TASKS.REQTIME} < '2:00p' then Otime[1]+Omeridian[2]
else
if {TASKS.REQTIME} > '1:59p' and {TASKS.REQTIME} < '3:00p' then Otime[2]+Omeridian[2]
else
if {TASKS.REQTIME} > '2:59p' and {TASKS.REQTIME} < '4:00p' then Otime[3]+Omeridian[2]
else
if {TASKS.REQTIME} > '3:59p' and {TASKS.REQTIME} < '5:00p' then Otime[4]+Omeridian[2]
else(etc. until I do all 24 hours in the day)

What ends up getting displayed is some times have both AM and PM(i figure this is because me > & < statement doesn't work with time the way I tought). It also doesn't show me all the times, only 2pm-8pm and thats it.

Can anyone assist me in creating a report that will show values per hour?

 

[dcm]

PlaidEmp:

Try these for size!

2 formulas - First one gets hour of task - Group report by this, Second one counts tasks per hour

@EachHour
NumberVar HourPortion:= Val(Mid({TASKS.REQTIME},1,Instr({TASKS.REQTIME},&quot;:&quot-1));
NumberVar AdjustedHour:= if Right({TASKS.REQTIME},1)=&quot;p&quot; Then HourPortion+12 else HourPortion;

@TaskCount
Count({TASKS.TASKID},EachHour)


Hope this helps David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner

 

[MalcolmW]

You could also convert the string in the format '10:59p' to a time, using:
TimeValue({TASKS.REQTIME})
Then, using Hour(), you can extract the hours only, expressed in a 24 clock.

@Each Hour
Hour(TimeValue({TASKS.REQTIME}))

@TaskCount
Count({TASKS.TASKID},EachHour) Malcolm