# 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 ?  Reply With Quote

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  Reply With Quote

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

4. One of which ranges?  Reply With Quote

5. The higher ones 50-64 etc  Reply With Quote

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

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  Reply With Quote

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  Reply With Quote

9. ## spreadsheet  Reply With Quote

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

updated spreadsheet working on column H  Reply With Quote

#### Posting Permissions

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