+ Reply to Thread
Results 1 to 2 of 2

COUNTIF nesting for turnaround times

  1. #1
    Registered User
    Join Date
    09-20-2019
    Location
    UK
    MS-Off Ver
    MS Office Home & Business 2013
    Posts
    1

    Question COUNTIF nesting for turnaround times

    Hi all,
    could someone possibly help me please.
    After i have worked out the formula, I want to get a bar chart/graph with the counts of the hours in the day when vehicles arrive to see what hours are the busiest hours.
    For example..
    Goods IN
    01:00:00 to 01:59:59 = count of 2
    02:00:00 to 02:59:59 = count of 4
    Goods OUT
    01:00:00 to 01:59:59 = count of 1
    02:00:00 to 02:59:59 = count of 0
    I need to do the above for both Goods IN and another for Goods OUT vehicles.
    I am struggling to create the formula other than =COUNTIFS($B$2:$B$13,">="&F2,$B$2:$B$13,"<"&G2) for the attached file.
    I know the format of times is wrong but wondered if it can also be picked up.

    thank you

    Stuio
    Attached Files Attached Files
    Last edited by stuio; 09-20-2019 at 10:40 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIF nesting for turnaround times

    Rows 3 to 13 are text that look like time. Select each column in turn. Data/Text to columns/Finish and format as time.

    I also changed the end times to round numbers as the < function wouldn't count anyhting occurring in the last second of an hour.

    =COUNTIFS($B$2:$B$13,">="&F2,$B$2:$B$13,"<"&G2)

    then works perfectly.

    Also.... why are you using a file format (.xls) that is 16 YEARS out of date, instead of .xlsx????
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 09-20-2019 at 12:30 PM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

+ 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. [SOLVED] Working out Turnaround Time based on three dates/times
    By david1987 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-11-2012, 12:54 PM
  2. Turnaround Time, Including saturdays, start times outside business hours.
    By alexi_987 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-15-2012, 07:00 PM
  3. Nesting IF(COUNTIF)
    By KRISHLAL in forum Excel General
    Replies: 5
    Last Post: 09-27-2011, 04:10 PM
  4. Turnaround times
    By morlindb in forum Excel General
    Replies: 11
    Last Post: 07-03-2009, 01:59 AM
  5. Nesting if and countif
    By saurya_s in forum Excel General
    Replies: 1
    Last Post: 10-31-2008, 06:28 PM
  6. Nesting countif?
    By cheesysocks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2008, 06:55 AM
  7. nesting with countif
    By Ross in forum Excel General
    Replies: 1
    Last Post: 09-13-2005, 09:05 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