Results 1 to 13 of 13

How do I create a formula to count multiple events in a set time period ex 8 am to 10 pm

Threaded View

  1. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,227

    Re: How do I create a formula to count multiple events in a set time period ex 8 am to 10

    Hi Wallardyce and welcome to the forum,

    Look at Column D for a new "Helper" column and the formula I think you need. Your times need a space between the last number and AM or PM. That is the first problem. Then converting the to time and rounding down to two hour shifts.
    See the big formula in D2. I've also done a Pivot Table and Chart so you can filter the group and see the times.
    =HOUR(TIMEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,"AM"," AM"),"PM"," PM"),11,25)))) - MOD(HOUR(TIMEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(C2,"AM"," AM"),"PM"," PM"),11,25)))),2)
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1