Newbie question

Caro

New member
Joined
Mar 1, 2014
Messages
12
Reaction score
0
Points
0
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 ?
 
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
 
thanks what happens if i want to add time to one of the ranges though ?
 
One of which ranges?
 
Add another IF statement covering that range and the appropriate result.
 
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 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


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

If not, what is wrong? Please detail.
 
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
10010517-1004221058-141003 (based on this number)14:10(this time)08:37
10040513-0928191016-14162314:1608:47
10090451-0800370947-11243711:2406:17
10100529-1016641110-14226414:2208: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

 
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...
 
Back
Top