CODEWITHSUNDEEP
exceltimerange
user3772147
user3772147

Reputation: 21

Start/End Times falling in ranges (and crossing over midnight)

I have a data set with start times in column B and end times in column C. I am currently using a sumproduct formula (=SUMPRODUCT(($B2<"07:00")*($C2>="07:59"))) to determine if the start/end time falls within time ranges (i.e. 07:00 to 07:59, 08:00 to 08:59, etc.). In the sumproduct formula the times are just examples (I will fill those with times in numerical format).

I have seen the sumproduct before and it's tried and tested, but I have yet to find a formula that does all of this AND accounts for if the start time is prior to midnight but the end time is after midnight.

The data set is for an event running from start to finish, and I ultimately want to determine what time ranges that event is running in by placing a 0/1 if the event is running in any given time interval.

My only fix to date is applying the sumproduct formula and then filtering on occurrences that cross over midnight and adapting the formula accordingly.

Any help with this would be greatly appreciated. Thank you in advance for your time.

Upvotes: 2

Views: 3630

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241573

Consider using a half open interval [Start,End) such that your ranges are 07:00 to 08:00, 08:00 to 09:00, etc.

  • This has the advantage of not skipping values like 07:59:30.

  • It also lets you do simple subtraction to get 1 hour instead of 59 minutes for the duration of the range.

  • Compare against the start of the range using <= or >= (depending on how you express it), but compare against the end of the range using < or >.

  • For time-only ranges that span midnight, you can just test to see if the value falls after the start or before the end.

Here is a complete formula. Replace [start], [end], and [value] as necessary.

=IF([start] <= [end], AND([start] <= [value], [value] < [end]), OR([value] >= [start], [value] < [end]))

Upvotes: 0

gtwebb
gtwebb

Reputation: 3011

First off I don't think you need sumproduct as you are not dealing with any sort of range that need summing so you could shorten your formula to ($B2>"07:00")*($C2<="07:59"). Note I also reversed the signs so it is actually between 7:00-7:59, also the formula won't work unless the 07:00 and 07:59 are stored in a cell because as written it is comparing a number to a text string and won't return the correct value.

For your actual question you need a second part to your function

=if(end<start,(B2<end)+(B2>start),(B2>start)*(B2<=end))

If the end time is less then the start time assume it goes past midnight so your time needs to be either after the start time or before the end time. If it does not go around midnight it has to be between them both.

Upvotes: 1

Related Questions