# Thread: Formula Help For Time Ranges and Duration Thanks: 0 Likes: 0

1. ## Formula Help For Time Ranges and Duration

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

2. ## Re: Formula Help For Time Ranges and Duration

Are you looking for something like this:

ABCDEF
1Time RangeRange IDDurationRange ID Assigned
20:3012:355
31:0023:596
41:3031:463
52:004
62:305
73:006

Sheet5

Worksheet Formulas
CellFormula
F2=VLOOKUP(E2,\$A\$2:\$B\$7,2)

3. ## Re: Formula Help For Time Ranges and Duration

Originally Posted by Aryatect
Are you looking for something like this:

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

Worksheet Formulas
Cell Formula
F2 =VLOOKUP(E2,\$A\$2:\$B\$7,2)
thank you for the quick reply. I think so, but it didnt work when i tried it.

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

4. ## Re: Formula Help For Time Ranges and Duration

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

5. ## Re: Formula Help For Time Ranges and Duration

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

6. ## Re: Formula Help For Time Ranges and Duration

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