IF function - criteria not working

Resolved · Urgent Priority · Version 365

Thu 26 Mar 2020, 14:41 Delegate 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:26 Trainer 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:21 Delegate 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.

Thu 26 Mar 2020, 17:23 Delegate Cecilia said...

RE: IF function - criteria not working

got it !!!!
In Year column, I've left as "date".
Awesome, many thanks for all the help :)

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Difference between Two Dates

The DATEDIF function computes the difference between two dates. The DATEDIF function is as follows:

=DATEDIF (Date1,Date2,Interval)

Please note that Date1 must be less than (earlier) or equal to Date2

Please note that Interval must be one of the following codes: "d" (in days), "m" (in months), "y" (in years) expressed in quotes

View all Excel hints and tips