If TIME falls between these bounds, label it X, etc.

[kestefon]

I have a massive spreadsheet with start and end times in columns A and B. I'd like to add a third column that shows a label for that time period.

For instance, any items between 8AM and 3PM would be called "X", and any items between 3PM and 6PM would be called "Y", etc. I could probably write a lengthy =IF formula, but I'm wondering if there's a more efficient way to do this. Maybe some sort of reference table and a VLookup?

Here's an example of what I'm trying to accomplish.

Excel Example - Add Labels.xlsx

[newdoverman]

This is based on the end time and not the start time so any time that crosses the boundary is counted as the end time. Enter in column C and copy down.

Please Login or Register  to view this content.

[6StringJazzer]

Your last example shows overlap and you want to match it to the "closest" fit. How do you define the closest fit?

Because your times ranges in the table to the right are more or less mutually exclusive and collectively exhaustive you could do this for a single value with a VLOOKUP but doing it for a time range gets complicated without very explicit requirements for overlap. The formula gets complicated because then you have to evaluate how much overlap there is in each interval and choose the one with the most overlap. And suppose you have overlap across more than two intervals?

[kestefon]

For overlap I'm thinking the label should be assigned based on how many hours it has in each interval. So 2 to 6 has 3 hours in Y and only 1 hour in X, therefore I would call it Y.

[kestefon]

Is that possible (regarding overlap)?

[kestefon]

Nevermind, I was able to break out the data in finer detail so that there are no longer overlaps. Thanks!

Now, returning to my original question...are IF statements the best/easiest way to do this?

[6StringJazzer]

Yes, you have to use IF statements in some form to do this, because you have to check for certain conditions and provide different results based on those conditions. The alternative to using formulas would be to use VBA macros.