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 ?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Mar 1, 2014
Messages
12
Reaction score
0
Points
0
thanks what happens if i want to add time to one of the ranges though ?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
One of which ranges?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Add another IF statement covering that range and the appropriate result.
 

Caro

New member
Joined
Mar 1, 2014
Messages
12
Reaction score
0
Points
0
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
Joined
Mar 1, 2014
Messages
12
Reaction score
0
Points
0
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


 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Is the latest sheet showing us that it worked? or not?

If not, what is wrong? Please detail.
 

Caro

New member
Joined
Mar 1, 2014
Messages
12
Reaction score
0
Points
0
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

 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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...
 
Top