# addition to my date formula...required

1. ## addition to my date formula...required

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
> if cell D7 = 7 or 8 then cell M7 to show 200
>
> The 5 6 7 8 refer to months ie May/June/July/August
> The 150/200 are £s"

2. ## Re: addition to my date formula...required

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,""))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Juco" <Me@nospam.fictional> wrote in message
news:qzOKd.15396\$v8.6863@fe2.news.blueyonder.co.uk...
> 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
> > if cell D7 = 7 or 8 then cell M7 to show 200
> >
> > The 5 6 7 8 refer to months ie May/June/July/August
> > The 150/200 are £s"

>
>

3. ## Re: addition to my date formula...required

> 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

=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,""))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

4. ## Re: addition to my date formula...required

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" <demechanik@yahoo.com> wrote in message
news:OgFRVQlBFHA.3596@TK2MSFTNGP12.phx.gbl...
> > 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
>
>
>

=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,""))
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

5. ## Re: addition to my date formula...required

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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Juco <Me@nospam.fictional> wrote in message
news:nK1Ld.19760\$v8.5359@fe2.news.blueyonder.co.uk...
> 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?

6. ## Re: addition to my date formula...required

Max,

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

Thanks very much for your help.

Juco

"Max" <demechanik@yahoo.com> wrote in message
news:e7ETAgrBFHA.936@TK2MSFTNGP12.phx.gbl...
> 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)
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> Juco <Me@nospam.fictional> wrote in message
> news:nK1Ld.19760\$v8.5359@fe2.news.blueyonder.co.uk...
> > 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?

>
>
>

7. ## Re: addition to my date formula...required

Great to hear that <g> !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Juco <Me@nospam.fictional> wrote in message
news:%f3Ld.17933\$B5.15071@fe1.news.blueyonder.co.uk...
> Max,
>
> You are correct I didn`r realise it was broken
> It works perfectly now.
>
> Thanks very much for your help.
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1