Calculate band on Hours/Time passed

[farhan11941234]

Please help me out to figure out a formula to calculate Date/Time Base Bins (See attached Image).Whenever The ID Creation(Column A) on Current Day and Time 3:00 PM to 6:00 PM, In Column C "> 3 to 6 PM" , Current Date and time between 6 to 7 PM in Column C result should be " > 6 to 7 PM", after 7:00 PM , in Column C should be "> 7:00 PM onwards.
when time > 14 Hours it should be >14 Hours and continue the this logic to >24 and >48 hours.
I would be very thankful to help me devolope the formula for this.

Sorry, for not using the XL2BB becuase of the admin Limitations.

Thanks

 

[Fluff]

There are few reasons to NOT use a full Excel Table instead of flat addresses.

That is you opinion, IMO there are lots of reason to NOT use tables.

 

[jdellasala]

That is you opinion, IMO there are lots of reason to NOT use tables.

That is not just my opinion, it's a fact.

• A fact that using Table references make a formula much clearer.
• A fact that as data is added to a Table the ranges grow with it.

If it's just a straight table with rows and columns of data, not taking advantage of Excel's Table feature is not something that someone who considers accuracy would do.

The only reasons to NOT use a table is the format does not lend itself to a Table format - as in Financial Statements, OR when a Spilled Array is used as it cannot be used in an Excel Table.

I was just trying to point out GOOD Excel practice. The opinion that there are "LOTS" of reasons is misguiding, and as the audience tends to have less experience, I think it's better to advocate good practices.

But go ahead, list your "LOTS" of reasons. I'm interested in seeing them.

 

[Fluff]

That is not just my opinion, it's a fact.

I would have to strongly disagree with this statement. I re-iterate it is just your opinion.

But go ahead, list your "LOTS" of reasons. I'm interested in seeing them.

Not in this thread as it is not relevant to the original question.

 

[farhan11941234]

Snapshot attached of the formula, Please suggest if the same can be acheived without using XLOOKUP for older version of Excel.

 

[jdellasala]

Snapshot attached of the formula, Please suggest if the same can be acheived without using XLOOKUP for older version of Excel.

VLOOKUP will do the same thing, it's just a bit more cumbersome to use. INDEX/MATCH will also work.

 

[farhan11941234]

if you could plesse share the Vlookup or INDEX/MATCH Version of it.

 

[Dave Patton]

Time.xlsm
	A	B	C	D	E	F
1	Time Stamp	Hours			Time	Category
2	14-Dec-22 0:12	38.8		Midnight to 3 AM	0	Midnight to 3 AM
3	14-Dec-22 3:27	35.6		3 AM to 6 AM	03:00	3 AM to 6 AM
4	13-Dec-22 17:23	45.7		3 PM to 6 PM	06:00	6 AM to 9 AM
5	14-Dec-22 4:48	34.2		3 AM to 6 AM	09:00	9 AM to Noon
6	13-Dec-22 17:37	45.4		3 PM to 6 PM	12:00	Noon to 3 PM
7	14-Dec-22 5:22	33.7		3 AM to 6 AM	15:00	3 PM to 6 PM
8	14-Dec-22 13:56	25.1		Noon to 3 PM	18:00	6 PM to 9 PM
9	13-Dec-22 6:00	51.6		3 AM to 6 AM	21:00	9 PM to Midnight
10
10d

Cell Formulas
Range	Formula
D2:D9	D2	=LOOKUP(MOD(A2,1),$E$2:$F$9)

VLookup

Time.xlsm
	A	B	C	D	E	F
1	Time Stamp	Hours			Time	Category
2	14-Dec-22 0:12	38.8		Midnight to 3 AM	0	Midnight to 3 AM
3	14-Dec-22 3:27	35.6		3 AM to 6 AM	03:00	3 AM to 6 AM
4	13-Dec-22 17:23	45.7		3 PM to 6 PM	06:00	6 AM to 9 AM
5	14-Dec-22 4:48	34.2		3 AM to 6 AM	09:00	9 AM to Noon
6	13-Dec-22 17:37	45.4		3 PM to 6 PM	12:00	Noon to 3 PM
7	14-Dec-22 5:22	33.7		3 AM to 6 AM	15:00	3 PM to 6 PM
8	14-Dec-22 13:56	25.1		Noon to 3 PM	18:00	6 PM to 9 PM
9	13-Dec-22 6:00	51.6		3 AM to 6 AM	21:00	9 PM to Midnight
10
10d

Cell Formulas
Range	Formula
D2:D9	D2	=VLOOKUP(MOD(A2,1),$E$2:$F$9,2,1)

 

[jdellasala]

I finally got it! Three versions too.

Book1
	G		I	J	K	L	M	N
1	Time Stamp		Xcat	Vcat	Icat		Time	Category
2	12/14/22 12:12 AM		Midnight to 3 AM	Midnight to 3 AM	Midnight to 3 AM		12:00:00 AM	Midnight to 3 AM
3	12/13/22 3:33 PM		3 PM to 6 PM	3 PM to 6 PM	3 PM to 6 PM		02:59:00 AM	Midnight to 3 AM
4	12/14/22 7:56 AM		6 AM to 9 AM	6 AM to 9 AM	6 AM to 9 AM		03:00:00 AM	3 AM to 6 AM
5	12/13/22 6:08 PM		6 PM to 9 PM	6 PM to 9 PM	6 PM to 9 PM		05:59:00 AM	3 AM to 6 AM
6	12/13/22 6:59 PM		6 PM to 9 PM	6 PM to 9 PM	6 PM to 9 PM		06:00:00 AM	6 AM to 9 AM
7	12/13/22 9:38 AM		9 AM to Noon	9 AM to Noon	9 AM to Noon		08:59:00 AM	6 AM to 9 AM
8	12/13/22 1:48 AM		Midnight to 3 AM	Midnight to 3 AM	Midnight to 3 AM		09:00:00 AM	9 AM to Noon
9	12/14/22 3:27 AM		3 AM to 6 AM	3 AM to 6 AM	3 AM to 6 AM		11:59:00 AM	9 AM to Noon
10	12/13/22 5:23 PM		3 PM to 6 PM	3 PM to 6 PM	3 PM to 6 PM		12:00:00 PM	Noon to 3 PM
11	12/14/22 4:48 AM		3 AM to 6 AM	3 AM to 6 AM	3 AM to 6 AM		02:59:00 PM	Noon to 3 PM
12	12/13/22 5:37 PM		3 PM to 6 PM	3 PM to 6 PM	3 PM to 6 PM		03:00:00 PM	3 PM to 6 PM
13	12/13/22 2:25 PM		Noon to 3 PM	Noon to 3 PM	Noon to 3 PM		05:59:00 PM	3 PM to 6 PM
14	12/14/22 11:16 PM		9 PM to Midnight	9 PM to Midnight	9 PM to Midnight		06:00:00 PM	6 PM to 9 PM
15	12/13/22 12:14 PM		Noon to 3 PM	Noon to 3 PM	Noon to 3 PM		08:59:00 PM	6 PM to 9 PM
16	12/14/22 11:20 AM		9 AM to Noon	9 AM to Noon	9 AM to Noon		09:00:00 PM	9 PM to Midnight
17	12/14/22 5:22 AM		3 AM to 6 AM	3 AM to 6 AM	3 AM to 6 AM		11:59:00 PM	9 PM to Midnight
18	12/14/22 1:56 PM		Noon to 3 PM	Noon to 3 PM	Noon to 3 PM
19	12/13/22 11:26 AM		9 AM to Noon	9 AM to Noon	9 AM to Noon
20	12/14/22 7:58 AM		6 AM to 9 AM	6 AM to 9 AM	6 AM to 9 AM
21	12/14/22 10:31 PM		9 PM to Midnight	9 PM to Midnight	9 PM to Midnight
Sheet2

Cell Formulas
Range	Formula
I2:I21	I2	=XLOOKUP(TIME(HOUR(G2),MINUTE(G2),0),TimeCats[Time],TimeCats[Category],"#####",1)
J2:J21	J2	=VLOOKUP(TIME(HOUR([@[Time Stamp]]),MINUTE([@[Time Stamp]]),0),TimeCats,2)
K2:K21	K2	=INDEX(TimeCats[Category],MATCH(TIME(HOUR([@[Time Stamp]]),MINUTE([@[Time Stamp]]),0),TimeCats[Time],1))

I looked at this using a sequence of 288 five minute time intervals with all 3 formulas, and it worked there too. Of note, INDEX-MATCH did NOT like a spilled array!
Anyway, hope that does it!

 

[jdellasala]

Time.xlsm
	A	B	C	D	E	F
1	Time Stamp	Hours			Time	Category
2	14-Dec-22 0:12	38.8		Midnight to 3 AM	0	Midnight to 3 AM
3	14-Dec-22 3:27	35.6		3 AM to 6 AM	03:00	3 AM to 6 AM
4	13-Dec-22 17:23	45.7		3 PM to 6 PM	06:00	6 AM to 9 AM
5	14-Dec-22 4:48	34.2		3 AM to 6 AM	09:00	9 AM to Noon
6	13-Dec-22 17:37	45.4		3 PM to 6 PM	12:00	Noon to 3 PM
7	14-Dec-22 5:22	33.7		3 AM to 6 AM	15:00	3 PM to 6 PM
8	14-Dec-22 13:56	25.1		Noon to 3 PM	18:00	6 PM to 9 PM
9	13-Dec-22 6:00	51.6		3 AM to 6 AM	21:00	9 PM to Midnight
10
10d

Cell Formulas
Range	Formula
D2:D9	D2	=LOOKUP(MOD(A2,1),$E$2:$F$9)

VLookup

Time.xlsm
	A	B	C	D	E	F
1	Time Stamp	Hours			Time	Category
2	14-Dec-22 0:12	38.8		Midnight to 3 AM	0	Midnight to 3 AM
3	14-Dec-22 3:27	35.6		3 AM to 6 AM	03:00	3 AM to 6 AM
4	13-Dec-22 17:23	45.7		3 PM to 6 PM	06:00	6 AM to 9 AM
5	14-Dec-22 4:48	34.2		3 AM to 6 AM	09:00	9 AM to Noon
6	13-Dec-22 17:37	45.4		3 PM to 6 PM	12:00	Noon to 3 PM
7	14-Dec-22 5:22	33.7		3 AM to 6 AM	15:00	3 PM to 6 PM
8	14-Dec-22 13:56	25.1		Noon to 3 PM	18:00	6 PM to 9 PM
9	13-Dec-22 6:00	51.6		3 AM to 6 AM	21:00	9 PM to Midnight
10
10d

Cell Formulas
Range	Formula
D2:D9	D2	=VLOOKUP(MOD(A2,1),$E$2:$F$9,2,1)

LOOKUP(MOD(A2,1)

Sweet!

 

[farhan11941234]

Thanks for other versions, but Vlookup and lookup version not working for me for unknown reason. I am trying to figure it why VLOOKUP is not working.