• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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
1 Solution
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now