Need to calculate the number of events that occurred with in time frames during the day

We have a database field that is a date/time field named AlarmDate.  I need a formula to look at the time in this field and determine if it is between 6am and 5:59:59pm or if it is between 6pm and 5:59:59am so that I can count the number of events that occurred in each time frame
Who is Participating?
vastoConnect With a Mentor Commented:
create a formula:
if Time(6,0 ,0 )< Time({AlarmDate}) AND Time({AlarmDate}) < Time (18, 0 , 0 ) then

it will return 1 if AlarmDate is between 6AM and 6PM and 0 if not.

I am using < Time (18, 0 , 0 ) instead of 5:59:59 PM you may want to change this
You create two formula fields :

If Hour({table.alarmdate})>=6 and Hour({table.alarmdate})<18 then 1 else 0

If Hour({table.alarmdate})<6 or Hour({table.alarmdate})>=18 then 1 else 0

Save each field with a meaningful name.

Then you just use Insert>Summary (twice) to Sum each of these fields.
fireprograms_techAuthor Commented:
Thank you so much this was a great solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.