Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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)

    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help For Time Ranges and Duration

    Quote Originally Posted by Aryatect View Post
    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. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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 ?
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •