Sorting records for a specific date/time period

[Bratbass]

I am trying to figure out how to sort daily records between the hours of 5:00PM - 7:00AM the folowing morning. It is easy to sort by days, but I am a little confused how to sort a specific time everyday, especially when it spans two days. Does anyone done this and can send me a copy of the code. Thanks

 

[boblarson]

If you have a date/time field that stores both date and time then sorting by that field will sort to the correct order.

 

[Bratbass]

Sorting it is easy, how do I do a report of just the records during the time span from 5:00PM - 7:00AM everyday

 

[racer25]

You could try the inverse and filter out everything between 7:00AM and 5:00PM

 

[Kempes]

create a formula field and add the following

hour(yourdatetimefield)

Now you should be able to choose this field in the selection criteria and use "is one of 5,6,7," or "between 5 and 7".

 

[Bratbass]

Thanks for the input Kempes. I am not sure yours would work as I am going from 5:00PM to 7:00AM the next morning.

Thanks Racer25 for the suggestion. You turned on a lightbulb that I didn't see. It would be so much easier to use the inverse method and just filter out 7:00 - 5:00 on the same day. I won't have to worry about spanning multiple days. It is so simple that I couldn't see it. Thanks a bunch

 

[Kempes]

Apologies, I lazily read this to be 5pm to 7pm of the same day.

Will the report be based on 5pm yesterday to 7am today?

In which case, choose you selection criteria against the date/time field and then formula and use this.

{Datetimefield} in DateTime (currentdate-1,time(17,00,00)) to DateTime (currentdate,time(07,00,00))

If not for yesterday to today, you should be able to adapt this to suit.

Hope this helps.

 

[Bratbass]

Hi Kempes,
This would actually work to as I would have to tweak it a little bit as the criteria has changed. Now they want a report for any records opened for the past year between the hours of 5:00PM to 7:00AM. With all the different time formula's to choose from, it got a little confusing. Thanks for the input. Now I need to put it to the test. Thanks again.

 

[Kempes]

OK, so you could split the criteria up then in that case.

Firstly select your datetimefield and choose between currentdate - 365 and currentdate.

Then you could create a new formula which will flag any records that meet the following criteria.

if time({datetimefield}) >= time(17,00,00) then true else
if time({datetimefield}) <= time(07,00,00) then true else false

This will flag each record, regardless of day, to true if they took place after 5pm or before 7am.

Then using selection criteria again, choose this field and select is true.

That should do the job.