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.
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
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.
