# Thread: Newbie question

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. 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. thanks what happens if i want to add time to one of the ranges though ?

4. One of which ranges?

5. The higher ones 50-64 etc

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

7. Originally Posted by Bob Phillips
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. ## 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. ## spreadsheet

10. https://docs.google.com/spreadsheets...it?usp=sharing

updated spreadsheet working on column H

Page 1 of 2 1 2 Last

#### Posting Permissions

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