+ Reply to Thread
Results 1 to 7 of 7

addition to my date formula...required

  1. #1
    Juco
    Guest

    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. #2
    Max
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Max
    Guest

    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

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

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



  4. #4
    Juco
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > > 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,""))
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > GMT+8, 1° 22' N 103° 45' E
    > xdemechanik <at>yahoo<dot>com
    > ----
    >
    >




  5. #5
    Max
    Guest

    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 <[email protected]> wrote in message
    news:[email protected]...
    > 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. #6
    Juco
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #7
    Max
    Guest

    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 <[email protected]> wrote in message
    news:%[email protected]...
    > Max,
    >
    > You are correct I didn`r realise it was broken
    > It works perfectly now.
    >
    > Thanks very much for your help.
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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