98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » IF function - criteria not working
IF function - criteria not working
Resolved · Urgent Priority · Version 365
Thu 26 Mar 2020, 14:41Delegate Cecilia said...
Cecilia has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course
Excel Dashboards for Business Intelligence course
IF function - criteria not working
Another help to fix a IF criteria:
Formula is: =IF(OR([@Scope]="No Trade",[@Scope]="Out of scope"),"Out",IF([@[Is it lapsed?]]="Yes","Lapsed",IF([@[Legacy SAQ last modified]]>=2019,"Wave2","Wave1")))
All criteria are working, apart from the last one, where I want to indicate "Wave 2" for dates that are >or= to 2019 and "Wave 1" to everything that is < or = to 2018.
When I apply the formula, the result does not differentiate the criteria for the years, bringing all results as "WAVE 2".
The only results as "Wave 1" it is indicated to the date: 00/01/1900.
The column "Legacy SAQ last modified" is formatted as Short Date, and contains dates between 2014 to 2019 and some 00/01/1900.
Appreciate the support!
Many thanks.
Thu 26 Mar 2020, 15:26Trainer Jens said...
RE: IF function - criteria not working
Hi Cecilia,
Thank you for the forum question.
If you have a short date in a column then you can test the year.
If you type the date 26 March 2020 and change the formatting to general or number format you will get the number 43916.
And if you type 01/01/1983 and change the format you will get 30317.
Your test if the date is 01/01/1983 will be 30317>=2019.
The best solution is to add a column with the year and then test the year column. You can use the Year function to extract the year from the date: =Year([@[Legacy SAQ last modified]])
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 26 Mar 2020, 17:21Delegate Cecilia said...
RE: IF function - criteria not working
Hi Jens,
Thanks, I believe I understood the logic, however I am not sure what is wrong now.
I have added a new column for the year, when I use the formula
=YEAR([@[Legacy SAQ last modified]]) the result is as per below example:
Legacy SAQ last modified Year (using =YEAR)
14/03/2020 12/07/1905
05/03/2019 11/07/1905
12/02/2019 11/07/1905
00/01/1900 14/03/1905
12/06/2018 10/07/1905
12/06/2018 10/07/1905
Once again, thanks for the support.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Difference between Two DatesThe DATEDIF function computes the difference between two dates. The DATEDIF function is as follows: |
