+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting based on date range

  1. #1
    RGB
    Guest

    Conditional formatting based on date range

    Hi,

    I am trying to create a formula which will;

    Firstly check if a date is in a range:

    * If not, then zero should be returned

    *If yes - the formula should check again to see if the date matches a
    specific date (only needs to match the month - not the exact day!)

    (If the date does match- Then formula should then return a value from
    another table.

    If it doesn't match the exact date, (but is still part of the original
    range) then a different $ value should be calculated based. This is based on
    a total $ value divided by a number of specified months.

    I need to work this out for many years information!

    So far I have;

    =IF(AND(F$1>=data!$K2, F$1<=data!$H2),
    (IF(G$1=data!K2),D2,(data!$I2/$C2),(""))

    Which doesn’t work - Anybody got any ideas?

    Many Thanks




  2. #2
    Guest

    Re: Conditional formatting based on date range

    Hi

    Try this:
    =IF(AND(F$1>=data!$K2, F$1<=data!$H2), IF(G$1=data!K2,D2,data!$I2/$C2),"")

    Andy.

    "RGB" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > I am trying to create a formula which will;
    >
    > Firstly check if a date is in a range:
    >
    > * If not, then zero should be returned
    >
    > *If yes - the formula should check again to see if the date matches a
    > specific date (only needs to match the month - not the exact day!)
    >
    > (If the date does match- Then formula should then return a value from
    > another table.
    >
    > If it doesn't match the exact date, (but is still part of the original
    > range) then a different $ value should be calculated based. This is based
    > on
    > a total $ value divided by a number of specified months.
    >
    > I need to work this out for many years information!
    >
    > So far I have;
    >
    > =IF(AND(F$1>=data!$K2, F$1<=data!$H2),
    > (IF(G$1=data!K2),D2,(data!$I2/$C2),(""))
    >
    > Which doesn't work - Anybody got any ideas?
    >
    > Many Thanks
    >
    >
    >




  3. #3
    Miguel Zapico
    Guest

    RE: Conditional formatting based on date range

    Could you post some example with numbers? The sintaxis of the formula is not
    wrong at first sight, so what do you mean by "doesn't work"?
    What puzzles me is why you are using both F1 and G1 in the comprobations,
    maybe with data it will be more clear (if that is the month comprobation,
    maybe changing G$1=data!K2 for MONTH(F$1)=MONTH(data!$K2) can help)

    Miguel.

    "RGB" wrote:

    > Hi,
    >
    > I am trying to create a formula which will;
    >
    > Firstly check if a date is in a range:
    >
    > * If not, then zero should be returned
    >
    > *If yes - the formula should check again to see if the date matches a
    > specific date (only needs to match the month - not the exact day!)
    >
    > (If the date does match- Then formula should then return a value from
    > another table.
    >
    > If it doesn't match the exact date, (but is still part of the original
    > range) then a different $ value should be calculated based. This is based on
    > a total $ value divided by a number of specified months.
    >
    > I need to work this out for many years information!
    >
    > So far I have;
    >
    > =IF(AND(F$1>=data!$K2, F$1<=data!$H2),
    > (IF(G$1=data!K2),D2,(data!$I2/$C2),(""))
    >
    > Which doesn’t work - Anybody got any ideas?
    >
    > Many Thanks
    >
    >
    >


  4. #4
    David Biddulph
    Guest

    Re: Conditional formatting based on date range

    "RGB" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > I am trying to create a formula which will;
    >
    > Firstly check if a date is in a range:
    >
    > * If not, then zero should be returned
    >
    > *If yes - the formula should check again to see if the date matches a
    > specific date (only needs to match the month - not the exact day!)
    >
    > (If the date does match- Then formula should then return a value from
    > another table.
    >
    > If it doesn't match the exact date, (but is still part of the original
    > range) then a different $ value should be calculated based. This is based
    > on
    > a total $ value divided by a number of specified months.
    >
    > I need to work this out for many years information!
    >
    > So far I have;
    >
    > =IF(AND(F$1>=data!$K2, F$1<=data!$H2),
    > (IF(G$1=data!K2),D2,(data!$I2/$C2),(""))
    >
    > Which doesn't work - Anybody got any ideas?


    1 If the value you are trying to test is in F1, you've used that in one
    place but you've used G1 in the next line. Or is G1 something you've
    created to deal with only needing the same month?

    2 You've used K2 as one limit for the range of dates, and you've also
    used it as the reference against which to check for the specific date; was
    that intentional?
    [But again it isn't clear how you've dealt with the fact that you're only
    looking for the same month. You may want to use the MONTH() function, but
    perhaps you also want to check for it being the same YEAR()?]

    3 The syntax of your second IF statement is illegal. Within the brackets
    you need the condition, then a comma then the result if true, then another
    comma and the result if false. You've closed the bracket after the
    condition. You don't need the bracket before the second IF, and you've also
    got additional sets of brackets which aren't necessary & which merely
    confuse you when you're looking to see what's where, so perhaps that second
    line should read:
    IF(G$1=data!K2,D2,data!$I2/$C2),"")

    4 You said the answer if it didn't fall within the first range should be
    zero, but you've given "" to return an empty result.
    --
    David Biddulph



+ 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