+ Reply to Thread
Results 1 to 5 of 5

Counting Time

  1. #1
    Forum Contributor
    Join Date
    11-28-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    129

    Counting Time

    I have a database that exports out a search but the time field is a different format, which I believe it is text format. When I do a countifs for the rep and between a time range it returns 0, which I know is obviously wrong. What formula can I do in the countifs to actually count them when the criteria is a different format? Because if I do a TIMEVALUE formula in column T and do my range as that column it works fine. But I am wanting to do this as an ongoing basis and do not want to have to worry about filling down a column, or if this ends up as a coworker using this sheet, I want to make as easy and simple as possible. I have attached my sample data as well.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,773

    Re: Counting Time

    Highlight the data in column S, then click on Data | Text-to-columns, then on the first panel of the dialogue box click on Finish. That should convert the text values to proper times, so you don't need column T.

    Hope this helps.

    Pete

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Counting Time

    hi ncurran217. try:
    =SUMPRODUCT((--$S$2:$S$143>=--B$1)*(--$S$2:$S$143<--C$1)*($R$2:$R$143=$A2))

  4. #4
    Forum Contributor
    Join Date
    11-28-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    129

    Re: Counting Time

    Thank you very much that worked perfectly!

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Counting Time

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help with counting how many are within a time range, for a specific time
    By snswilliams in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-29-2013, 02:19 PM
  2. Counting time within time periods!
    By thegooser123 in forum Excel General
    Replies: 8
    Last Post: 09-06-2011, 08:33 PM
  3. Counting time
    By TheGame0135 in forum Excel General
    Replies: 37
    Last Post: 07-14-2010, 07:49 AM
  4. Counting time on a job.
    By coolhit in forum Excel General
    Replies: 1
    Last Post: 07-13-2010, 08:05 AM
  5. Counting Time
    By sldobbs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2007, 05:17 PM

Tags for this Thread

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