+ Reply to Thread
Results 1 to 6 of 6

If Statement and Dates

  1. #1
    Toys
    Guest

    If Statement and Dates

    I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
    12/31/05. I want to use a conditional if statement to evaluate if the date
    is less than a given day, for example: =IF(A1<12/15/05,"Less","Greater").
    Therefore the first 14 should result in "Less" and the last 17 should result
    in "Greater". My reslut is returning "Greater" for all of them. Any ideas
    why? Two additional things: 1st, if I put the date in column B and then do
    =IF(A1<B1,"Less","Greater") then it works fine, its only a problem when I
    enter it manually. 2nd, this would be fine, but I'm trying to use a Macro
    with an Input box where I enter the date (a variable) which slots into my If
    statement. Any suggestions?

    Thanks.

  2. #2
    Ron Rosenfeld
    Guest

    Re: If Statement and Dates

    On Mon, 19 Dec 2005 04:34:02 -0800, "Toys" <Toys@discussions.microsoft.com>
    wrote:

    >I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
    >12/31/05. I want to use a conditional if statement to evaluate if the date
    >is less than a given day, for example: =IF(A1<12/15/05,"Less","Greater").
    >Therefore the first 14 should result in "Less" and the last 17 should result
    >in "Greater". My reslut is returning "Greater" for all of them. Any ideas
    >why? Two additional things: 1st, if I put the date in column B and then do
    >=IF(A1<B1,"Less","Greater") then it works fine, its only a problem when I
    >enter it manually. 2nd, this would be fine, but I'm trying to use a Macro
    >with an Input box where I enter the date (a variable) which slots into my If
    >statement. Any suggestions?
    >
    >Thanks.


    The problem is that, when parsing your IF statement, Excel does not know if you
    mean a date, or if you mean 12 divided by 15 divided by 5. Since you have no
    quote marks around your date representation, Excel assumes you mean the
    arithmetic process and not a date.

    Try:

    A1<"12/15/05"

    Most unambiguous would be

    A1<DATE(2005,12,15)


    --ron

  3. #3
    Toys
    Guest

    Re: If Statement and Dates

    Thanks for the quick response. I tried with quotes, but I think it
    recognizes it as text because the opposite problem occurs, it evaluates it as
    all true ("Less"). I'll try your other suggestion and let you know.

    Toys

    "Ron Rosenfeld" wrote:

    > On Mon, 19 Dec 2005 04:34:02 -0800, "Toys" <Toys@discussions.microsoft.com>
    > wrote:
    >
    > >I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
    > >12/31/05. I want to use a conditional if statement to evaluate if the date
    > >is less than a given day, for example: =IF(A1<12/15/05,"Less","Greater").
    > >Therefore the first 14 should result in "Less" and the last 17 should result
    > >in "Greater". My reslut is returning "Greater" for all of them. Any ideas
    > >why? Two additional things: 1st, if I put the date in column B and then do
    > >=IF(A1<B1,"Less","Greater") then it works fine, its only a problem when I
    > >enter it manually. 2nd, this would be fine, but I'm trying to use a Macro
    > >with an Input box where I enter the date (a variable) which slots into my If
    > >statement. Any suggestions?
    > >
    > >Thanks.

    >
    > The problem is that, when parsing your IF statement, Excel does not know if you
    > mean a date, or if you mean 12 divided by 15 divided by 5. Since you have no
    > quote marks around your date representation, Excel assumes you mean the
    > arithmetic process and not a date.
    >
    > Try:
    >
    > A1<"12/15/05"
    >
    > Most unambiguous would be
    >
    > A1<DATE(2005,12,15)
    >
    >
    > --ron
    >


  4. #4
    Ron Rosenfeld
    Guest

    Re: If Statement and Dates

    On Mon, 19 Dec 2005 04:57:02 -0800, "Toys" <Toys@discussions.microsoft.com>
    wrote:

    >Thanks for the quick response. I tried with quotes, but I think it
    >recognizes it as text because the opposite problem occurs, it evaluates it as
    >all true ("Less"). I'll try your other suggestion and let you know.
    >
    >Toys


    The DATE(yr,month,day) is certainly the most unambiguous; but you could try:

    DATEVALUE("12/01/2005")





    >
    >"Ron Rosenfeld" wrote:
    >
    >> On Mon, 19 Dec 2005 04:34:02 -0800, "Toys" <Toys@discussions.microsoft.com>
    >> wrote:
    >>
    >> >I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
    >> >12/31/05. I want to use a conditional if statement to evaluate if the date
    >> >is less than a given day, for example: =IF(A1<12/15/05,"Less","Greater").
    >> >Therefore the first 14 should result in "Less" and the last 17 should result
    >> >in "Greater". My reslut is returning "Greater" for all of them. Any ideas
    >> >why? Two additional things: 1st, if I put the date in column B and then do
    >> >=IF(A1<B1,"Less","Greater") then it works fine, its only a problem when I
    >> >enter it manually. 2nd, this would be fine, but I'm trying to use a Macro
    >> >with an Input box where I enter the date (a variable) which slots into my If
    >> >statement. Any suggestions?
    >> >
    >> >Thanks.

    >>
    >> The problem is that, when parsing your IF statement, Excel does not know if you
    >> mean a date, or if you mean 12 divided by 15 divided by 5. Since you have no
    >> quote marks around your date representation, Excel assumes you mean the
    >> arithmetic process and not a date.
    >>
    >> Try:
    >>
    >> A1<"12/15/05"
    >>
    >> Most unambiguous would be
    >>
    >> A1<DATE(2005,12,15)
    >>
    >>
    >> --ron
    >>


    --ron

  5. #5
    Toys
    Guest

    Re: If Statement and Dates

    Date(yyyy,mm,dd) worked perfectly. I had no problems using it with my macro
    either. Thanks

    "Ron Rosenfeld" wrote:

    > On Mon, 19 Dec 2005 04:57:02 -0800, "Toys" <Toys@discussions.microsoft.com>
    > wrote:
    >
    > >Thanks for the quick response. I tried with quotes, but I think it
    > >recognizes it as text because the opposite problem occurs, it evaluates it as
    > >all true ("Less"). I'll try your other suggestion and let you know.
    > >
    > >Toys

    >
    > The DATE(yr,month,day) is certainly the most unambiguous; but you could try:
    >
    > DATEVALUE("12/01/2005")
    >
    >
    >
    >
    >
    > >
    > >"Ron Rosenfeld" wrote:
    > >
    > >> On Mon, 19 Dec 2005 04:34:02 -0800, "Toys" <Toys@discussions.microsoft.com>
    > >> wrote:
    > >>
    > >> >I have a list of sequential dates in column A; let's 12/01/05 (Dec 01, 05) to
    > >> >12/31/05. I want to use a conditional if statement to evaluate if the date
    > >> >is less than a given day, for example: =IF(A1<12/15/05,"Less","Greater").
    > >> >Therefore the first 14 should result in "Less" and the last 17 should result
    > >> >in "Greater". My reslut is returning "Greater" for all of them. Any ideas
    > >> >why? Two additional things: 1st, if I put the date in column B and then do
    > >> >=IF(A1<B1,"Less","Greater") then it works fine, its only a problem when I
    > >> >enter it manually. 2nd, this would be fine, but I'm trying to use a Macro
    > >> >with an Input box where I enter the date (a variable) which slots into my If
    > >> >statement. Any suggestions?
    > >> >
    > >> >Thanks.
    > >>
    > >> The problem is that, when parsing your IF statement, Excel does not know if you
    > >> mean a date, or if you mean 12 divided by 15 divided by 5. Since you have no
    > >> quote marks around your date representation, Excel assumes you mean the
    > >> arithmetic process and not a date.
    > >>
    > >> Try:
    > >>
    > >> A1<"12/15/05"
    > >>
    > >> Most unambiguous would be
    > >>
    > >> A1<DATE(2005,12,15)
    > >>
    > >>
    > >> --ron
    > >>

    >
    > --ron
    >


  6. #6
    Ron Rosenfeld
    Guest

    Re: If Statement and Dates

    On Wed, 21 Dec 2005 00:15:02 -0800, "Toys" <Toys@discussions.microsoft.com>
    wrote:

    >Date(yyyy,mm,dd) worked perfectly. I had no problems using it with my macro
    >either. Thanks
    >


    You're very welcome. Thanks for the feedback.
    --ron

+ 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