Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Newbie question

  1. #1

    Newbie question



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all
    I have a cell that contains a number (formatted into "hh:mm")
    What I want to be able to do is add or subtract depending on a value in a different cell
    So if cell a =1 to 4 subtract 20 mins
    If cell a = 13 to 21 subtract 15 mins
    If cell a = 48 to 60 add 10 mins

    How do I go about this ?

  2. #2
    If the time is in B2, and the value in A2, use

    =B2-(IF(AND(A2>=1,A2<=4),20,IF(AND(A2>=13,A2<=21),15,IF(AND(A2>=48,A2<=60),10,0))))/1440

  3. #3
    thanks what happens if i want to add time to one of the ranges though ?

  4. #4
    One of which ranges?

  5. #5
    The higher ones 50-64 etc

  6. #6
    Add another IF statement covering that range and the appropriate result.

  7. #7
    Quote Originally Posted by Bob Phillips View Post
    Add another IF statement covering that range and the appropriate result.
    =G7,SUM(IF(AND(F7>=1,F7<=10),"0.20.0",IF(AND(F7>=19,F7<=-30),"0.15.0",IF(AND(F7>=42,F7<=64),"0.10.0"))))/1440

    so I've got this want to subtract in the first two values but add time in the last value
    cheers for the help also I'm learning slowly

  8. #8

    reply workbook to follow

    =G8-(IF(AND(F8>=1,F8<=18),10,IF(AND(F8>=19,F8<=-30),15,G8+(IF(AND(F8>=42,F8<=67),10))))/1440)

    changed it slightly
    this works on the 1-10 but not the 19 to 30 !
    and it still ducting on the higher 42 to 67
    am i just writing it wrong ?

    seems to work on my first line see attached example
    G is B in this case
    And the numbers in column A go from 1 to 67 with some not used
    42--67 add ten mins
    1 to19 deduct 10
    31 to 41 deduct 15



  9. #9

  10. #10

Page 1 of 2 1 2 LastLast

Posting Permissions

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