# Newbie question

#### Caro

##### New member
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

#### Bob Phillips

##### Super Moderator
Staff member
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

#### Caro

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

#### Bob Phillips

##### Super Moderator
Staff member
One of which ranges?

#### Caro

##### New member
The higher ones 50-64 etc

#### Bob Phillips

##### Super Moderator
Staff member
Add another IF statement covering that range and the appropriate result.

#### Caro

##### New member
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

#### Caro

##### New member

=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

#### NBVC

##### Super Moderator
Staff member
Is the latest sheet showing us that it worked? or not?

If not, what is wrong? Please detail.

#### Caro

##### New member
Is the latest sheet showing us that it worked? or not?

If not, what is wrong? Please detail.
does not work !
I can make it work if i use just two (IF(AND statements
But not with the third
 1001 0517-1004 22 1058-1410 03 (based on this number) 14:10(this time) 08:37 1004 0513-0928 19 1016-1416 23 14:16 08:47 1009 0451-0800 37 0947-1124 37 11:24 06:17 1010 0529-1016 64 1110-1422 64 14:22 08:37
MID(D2,6,2)*VALUE("01:00:00")+RIGHT(D2,2)*VALUE("00:01:00")+(IF(AND(E2>=1,E2<=28),14,(IF(AND(E2>29,E2<41),45,(IF(AND(E2>=42,E2<=64),30))))/1440)

not all the based numbers are repensited some are missing
hope thats a better explination

#### NBVC

##### Super Moderator
Staff member
I think some of your parentheses are out of place, try:

=MID(D2,6,2)*VALUE("01:00:00")+RIGHT(D2,2)*VALUE("00:01:00")+(IF(AND(E2>=1,E2<=28),14,IF(AND(E2>29,E2<41),45,IF(AND(E2>=42,E2<=64),30))))/1440

not sure if that fixes it or not, but better to start clearly defining...