Formula Help For Time Ranges and Duration

[dpg010]

Hello. I have 6 times ranges. I have durations of videos, and im trying to assign range ID's to them. Basically, i dont know if i should use IF, or countIFs and vlookup. Example, if you look at the workbook and look at E2, it would fall under range ID 5. Any help is appreciated.

https://postimg.cc/TLKQtPXy

 

[Aryatect]

Are you looking for something like this:

Book1
	A	B	C	D	E	F
1	Time Range	Range ID			Duration	Range ID Assigned
2	0:30	1			2:35	5
3	1:00	2			3:59	6
4	1:30	3			1:46	3
5	2:00	4
6	2:30	5
7	3:00	6
Sheet5

Cell Formulas
Range	Formula
F2	=VLOOKUP(E2,$A$2:$B$7,2)

 

[dpg010]

Are you looking for something like this:

	A	B	C	D	E	F
Time Range	Range ID	Duration	Range ID Assigned

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2:35[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3:59[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1:30[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1:46[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2:00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2:30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=VLOOKUP(E2,$A$2:$B$7,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thank you for the quick reply. I think so, but it didnt work when i tried it.

https://i.postimg.cc/fbjGrgNy/1.png

 

[Aryatect]

is the time range in column A stored as number and in same number format as in column E ?

 

[dpg010]

When i click on the times ranges, cell A2, it shows as an actual time stamp: Like 12:00

 

[Aryatect]

If you convert the number format for cell A2 to General then it should be 0.020833 and E2 should be 0.1076389