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

Posted on 2011-10-21
Last Modified: 2012-05-12
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
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

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

    Author Closing Comment

    Thank you so much this was a great solution

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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 ( If you're interested in additional methods for monitoring bandwidt…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now