If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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(A112/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(A1B1,"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
|
|||
|
|||
If Statement and Dates
On Mon, 19 Dec 2005 04:34:02 -0800, "Toys"
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(A112/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(A1B1,"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 A1DATE(2005,12,15) --ron |
#3
|
|||
|
|||
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" 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(A112/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(A1B1,"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 A1DATE(2005,12,15) --ron |
#4
|
|||
|
|||
If Statement and Dates
On Mon, 19 Dec 2005 04:57:02 -0800, "Toys"
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" 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(A112/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(A1B1,"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 A1DATE(2005,12,15) --ron --ron |
#5
|
|||
|
|||
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" 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" 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(A112/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(A1B1,"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 A1DATE(2005,12,15) --ron --ron |
#6
|
|||
|
|||
If Statement and Dates
On Wed, 21 Dec 2005 00:15:02 -0800, "Toys"
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
problem between dates in where statement 0-obs | [email protected] | Running & Setting Up Queries | 1 | October 20th, 2005 09:15 PM |
Please Help ! Queries for Dates, if statement... | Bill | Running & Setting Up Queries | 3 | September 17th, 2005 05:15 PM |
dates and if statement work on one sheet, not on another | John Brown | Worksheet Functions | 3 | September 8th, 2005 12:58 AM |
SQL Statement for Range of Dates | JeremyH1982 | General Discussion | 3 | July 29th, 2005 03:44 PM |
dates and between statement | gaxmancCO | Using Forms | 1 | September 9th, 2004 10:45 PM |