Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: "If Formula" for elasped time

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to do an if formula for elasped time. If 20 minutes have passed I want "1" to appear 40 minutes "2", 60 minutes "3" and so on.

    I have the elasped time thing done. But I can't seem to get the if function to work, can someone please help. Thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use =TRUNC(A1/20) where A1 contains your elapsed time as minutes. If A1 contains a time value (e.g., 0:20) use =TRUNC(A1*72). An IF function isn't necessary.

    [ This Message was edited by: Mark W. on 2002-03-21 08:53 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,427
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You did not state if cell contained
    number or time.

    with number
    =INT(A48/20)*1

    with time
    =INT(MINUTE(A50)/20)*1

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    with time
    =INT(MINUTE(A50)/20)*1
    What if the elapsed time value is 1:40 (1 hour and 40 minutes)? Or 60 minutes which Excel will represent as 1:00?

    What does the "*1" do?

    [ This Message was edited by: Mark W. on 2002-03-21 09:21 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,427
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK

    revise to

    numbers =INT(A48/20)

    times =INT(MINUTE(A50)+HOUR(A50)*60)/20

Some videos you may like

User Tag List

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
  •