If statments w/ <= dates
Solved/Closed
andrea
-
8 Mar 2010 à 16:28
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 10 Mar 2010 à 13:13
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 10 Mar 2010 à 13:13
Related:
- If statments w/ <= dates
- Iphone 14 release dates - Home - IOS
- Apple iphone 13 release dates - Guide
- Vivatech 2024 dates - Guide
- Blackhat dates - Guide
- Excel if function with dates - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
8 Mar 2010 à 17:21
8 Mar 2010 à 17:21
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, ""))))
8 Mar 2010 à 17:36
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.
8 Mar 2010 à 19:13
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.
9 Mar 2010 à 10:05
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.
9 Mar 2010 à 12:11
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.
10 Mar 2010 à 13:08
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?