Solved

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

Posted on 2011-10-21
192 Views
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
0
Question by:fireprograms_tech

LVL 18

Accepted Solution

create a formula:
if Time(6,0 ,0 )< Time({AlarmDate}) AND Time({AlarmDate}) < Time (18, 0 , 0 ) then
1
ELSE
0

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
0

LVL 77

Expert Comment

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.
0

Author Closing Comment

Thank you so much this was a great solution
0

## Featured Post

### Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…