addition to my date formula...required

[Juco]

Ok I hope last piece of the jigsaw..I have started a new post as everything
up to this point is working ok.
The formula below works fine but need to add to it, if at all possible, I
could do this manually if its too complicated.


=CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"") This formula in cell M7 and
works great.
This is a booking form and depending on the months depends on the price ie 5
6 7 8 are months and 150 & 200 are £s If someone books last week of month 6
and first week of month 7 then I would need to add a further £50 as this is
across the 2 cost bands The trigger for that cell would be H7 as this would
show it was clicking into the next month.
start date which is cell D7 the person puts 5,6,7 or 8 (mm)
end date which is cell H7 the person puts 5,6,7 or8 (mm)
If D7 has 6 (mm) and H7 has 7 (mm) then I need to add a further £50 to
the total cost (I think would be ok to add £25 to each week) as they have
now moved from the £150 to the £200 price band.

thanks


" if cell D7 = 5 or 6 then cell M7 to show 150

 

[Max]

Try in M7:

=IF(H7=D7+1,CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")+50,CHOOSE(MATCH(D7,{
0;5;7;9},1),"",150,200,""))

 

[Max]

Try in M7:

=IF(H7=D7+1,CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")+50,CHOOSE(MATCH(D7,{
0;5;7;9},1),"",150,200,""))

Correction to formula suggested, sorry

Try instead in M7:

=IF(AND(D7=6,H7=7),CHOOSE(MATCH(D7,{0;5;7;9},1),"",150,200,"")+50,CHOOSE(MAT
CH(D7,{0;5;7;9},1),"",150,200,""))

 

[Juco]

Max when I put the corrected formula in it comes up #NAME? unless I use
months 6 snd 7 in which case it gives the correct answer . If both dates are
in month 6 so 6 6 or 7 and 8 I get #NAME?

 

[Max]

Think you probably got hit by several inevitable line breaks/wraps when you
copied and pasted the formula from the post. Try pasting *direct* into the
fornula bar for M7, then correct the obvious line breaks via using
backspace/delete key to restore where the formula gets "chopped". I've just
retested it here and it works ok. If you still have difficulty, send me a
copy of your book, and I'll set it up for you.

Either:
demechanik <at>yahoo<dot>com
or
xdemechanik <at>yahoo<dot>com
(both valid)

 

[Juco]

Max,

You are correct I didn`r realise it was broken
It works perfectly now.

Thanks very much for your help.

Juco

 

[Max]

Great to hear that <g> !
Thanks for the feedback ..