Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2006
    Posts
    39

    Unanswered: Counting TIME Only

    Hey All,

    What I'm looking to do is Count cells where time is equal to or exceeds 4:30 PM, as 1. (Left Column) Also, do the same in the right column, but only count the cells where time is equal to or exceeds 4:30 PM, but is no greater than 5:00PM, as 1.
    The columns in the screenshot have dates and times. Can anyone provide me with a formula solution?

    Thank you!
    Attached Thumbnails Attached Thumbnails 000.PNG  

  2. #2
    Join Date
    Jan 2008
    Posts
    19
    How about using the IF statement? Have you tried that?

  3. #3
    Join Date
    Apr 2008
    Posts
    2

    Does anyone really know what time it is?

    Hi - I played around with this briefly. Set up a second range of cells where the top left cell corresponds to the top left cell of the original range(i.e.-B2).
    Formula =ROUNDDOWN(B2,0)-B2
    copy this formula to all of the cells in the new range. This essentially removes the date from the values. Then in a cell outside of the ranges, use a =(COUNTIF(range:range,">4:29")-COUNTIF(range:range,">5:01))

    to count all of the cells with a time value that you indicated wanting to know.

    Good luck

  4. #4
    Join Date
    Apr 2008
    Posts
    2

    Formula Correction

    =B2-ROUNDDOWN(B2,0)
    round off date to leave only time

    =(COUNTIF(G2:J25,">4:29")-COUNTIF(G2:J25,">5:00"))
    all greater than 4:29 minus all greater than 5:00

  5. #5
    Join Date
    Dec 2006
    Posts
    39
    Excelorator,

    YOU flippin ROCK!
    Thanks a ton....that will do the trick. Dam - that's awesome!

    MillB

Posting Permissions

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