Group by Time Range for Month with field using DateTime UTC 1

[JULY1]

I would like to group all my records for an entire month by time ranges. Such as all calls for the month of May made between the hours of 07:00:00 to 09:30:00.

I am currently using the following formula to convert the UTC timedate field which is in seconds (example: 320,251,407) to a standard time and date (example: 5/1/2002 7:10:07AM) and am unable to seperate the date from the time.

My Formulated field for "StartTime" is as follows:
{CallDetailRecord.dateTimeConnect} / 86400 + DateTime (1969,12,31,20,00,00)

I am able to group within a time range for one (1) specific day, but not for multiple days such as for an entire month.

 

[lbass]

Try wrapping your datetime in time() as in:

if time({@datetime}) in time(7,0,0) to time(9,30,0) then "7 AM to 9:30 AM" else
if time({@datetime}) in time(9,30,01) to time(11,0,0) then
"9:30 AM to 11:00 AM" else//etc.

You can then group on the formula. You might need to use specified order in order to get the desired group order.

-LB

 

[JULY1]

I have created a @TimeRange formula and have gone by your instructions and used this formula as a group. I also had to use the specified order. It does break down the time by time groups but . . . it overrides my formula for another field to use "is in the period of last full month". So now I am getting all the records in the database.

How can I get it to just display records for the Month of May?

Note: Please forgive me if I seem to be overlooking the obvious. I'm not very experienced with this. Your help is GREATLY appreciated LB.

 

[lbass]

If the entire report is only for the last full month, then in the record selection formula use:

{table.date} in lastfullmonth

Since each record containing a time field will also contain a date field, only the times falling in the lastfullmonth will be used in the report.

-LB

 

[JULY1]

I did do that except it was:

@{table.date} in lastfullmonth

Then I received the following message when I went to Select Expert to check if it was in correctly (since I was getting all months):

"Composite expression. Please use the formula editor to do editing."

I don't know why it didn't take.

But, I was able to group by date and use the specified order so that I could see all records for May 2005.

I've never used the "Specified Order" until you mentioned it. Thanks alot LB.