If statments w/ <= dates
Solved/Closed
andrea
-
Mar 8, 2010 at 04:28 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 10, 2010 at 01:13 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 10, 2010 at 01:13 PM
Related:
- If statments w/ <= dates
- Gamescom 2023 dates - Guide
- Defcon 2023 dates - Guide
- Steam sale dates - Guide
- Wimbledon dates - Guide
- Nintendo direct 2023 dates - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 8, 2010 at 05:21 PM
Mar 8, 2010 at 05:21 PM
Well lets see where should I start :p~
1. Date could be an issue .01-01-2010 looks like a date, but may not be a date. It may be mm/dd/yyyy or dd/mm/yyyy format
2. Your if statements are wrong. When this for example will be true A2<="01-01-1901",A2>="31-12-1963". Either A2 less than 1901 or more than 1963 but both can never be true. You should have A2>="01-01-1901",A2<="31-12-1963"
3. At least on my system, if i enclose date in "", it cease to be a valid date for comparison purpose. So have the date without ""
Here is a simpler way [but make sure that excel treats that A2 value as date which you can easily see if you try to change the format of date into some thing else. Date will format, but text date (some thing that looks like date but is not date to excel) will not change the format and will continue to look same]
=IF(TRIM(A2)="", "",IF(YEAR(A2) <= 1963, 2, IF(YEAR(A2)<=1970, 3, IF(A2 <=TODAY(), 5, ""))))
1. Date could be an issue .01-01-2010 looks like a date, but may not be a date. It may be mm/dd/yyyy or dd/mm/yyyy format
2. Your if statements are wrong. When this for example will be true A2<="01-01-1901",A2>="31-12-1963". Either A2 less than 1901 or more than 1963 but both can never be true. You should have A2>="01-01-1901",A2<="31-12-1963"
3. At least on my system, if i enclose date in "", it cease to be a valid date for comparison purpose. So have the date without ""
Here is a simpler way [but make sure that excel treats that A2 value as date which you can easily see if you try to change the format of date into some thing else. Date will format, but text date (some thing that looks like date but is not date to excel) will not change the format and will continue to look same]
=IF(TRIM(A2)="", "",IF(YEAR(A2) <= 1963, 2, IF(YEAR(A2)<=1970, 3, IF(A2 <=TODAY(), 5, ""))))
Mar 8, 2010 at 05:36 PM
i tryed the correction of both the > and " and had no luck..... it came back with all 5's when there should be 2's and 3's as well.
the column is formated dd-mm-yyyy so i tryed your formuls and it just came back blank.....
im new to nested statments and im completly frustrated. any and all halp is greatly appreciated.
Mar 8, 2010 at 07:13 PM
Presuming Column A is where all the dates are, then you can do this
1. Select Column A
2. Click on "Data" and choose "Text to Column"
3. Choose "fixed Width"
4. make sure that the line that shows up on next screen completely covers the date (most probably position 10)
5. Select "Column Data Format" to be 'Date' with appropriate date format (it seems your is DMY)
6. Click Finish
At this point your the values in column A are truly converted in dates
now use the formula that I earlier gave you.
Mar 9, 2010 at 10:05 AM
i was just going into the format cells option to set my date formate, i did not know to go in that way. i really do appreciate all your help.
now i get to build a other statment based on the results of this one. (hopefully I dont have more issues)
thank you again.
im self taught with excel so i spend ALOT of time reading trying to figure things out.
Mar 9, 2010 at 12:11 PM
You mentioned that you were about to format the column to change date format. I think I might not have been able to explain the issue to you. In excel dates are not like 1/2/2010 but rather are stored in serial number. Using format, we change that number to various date format. Now on times, you will encounter situation like yours where the column looks like a date, but is actually not a date but just a text that gives appearance of being a date. You will find under such situation that neither the formatting works nor any of date functions (like dateserial etc) This is a dead give away that what you have is not date value when it comes to excel. The method i mentioned above allows you to take that text and convert into serial number that would result in you having a value that looks like a date and more importantly excel consider it as a date too. Hope I am able to explain it better than the last time.
Mar 10, 2010 at 01:08 PM
one more question..... i have formated the entire column as directed, the populated cells took the formating, how ever the empty cells will not hold the formating. every time new data is entered it has to be re formated. im not quite sure why, any ideas?