Counting time ranges within a range

**Ducatisto** · 10-16-2008, 03:02 AM

I have a column of start times which are entered as per 24hr clock and what I am trying to do is to is to sort the times out into ranges;

00:01 to 06:59, 07:00 to 19:00 and 19:01 to 00:00

The range is T7:T488 and here is my first formula for 00:01 to 06:59 which works, it correctly finds 35 entries;

=SUMPRODUCT(--(T7:T488>=I496),--(T7:T488<=I498))-COUNTBLANK(T7:T488)

I have the time ranges set up for the two final formulas but I have massive negative values returned which is obviously not correct;

=SUMPRODUCT(--(T7:T488>=I497),--(T7:T488<=I500))-COUNTBLANK(T7:T488)

This returns a value of -289 whilst the other a value of -341.

I may have gone about it the wrong way as I am trying to do things without too much assistance but this has me stumped. I would be most grateful to be shown the correct way, thank you.

**oldchippy** · 10-16-2008, 03:45 AM

Hi,

Does this help? The time periods are

00:01 06:59
07:00 19:00
19:01 23:59

**Ducatisto** · 10-16-2008, 05:27 AM

Thank you for that. It came to the common conclusion even though it logically looked correct Excel hates midnight!

Many thanks for your help.

PS If this helps I have a macro that if indeed anyone has an already populated file and needs a similar solution this will add a second to all issues of midnight.

Sub change_midnight()

    Cells.Replace What:="00:00:00", Replacement:="00:00:01", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
End Sub

**oldchippy** · 10-16-2008, 05:41 AM

Shouldn't the replacement be 23:59:59, if you were counting 19:01 to midnight?

**Ducatisto** · 10-17-2008, 02:16 AM

Originally Posted by oldchippy

Shouldn't the replacement be 23:59:59, if you were counting 19:01 to midnight?

You are correct on that part, it was for current populated cells that referred to start times. From this moment on finish times that should be midnight will be entered as "23:59".

Thank you for your help, I really could not have got a lot further without this forum and all of its contributors.