Change Cell Contents if Cell Date is in Previous Month / 4 Months

Mazzic

New Member
Joined
Sep 17, 2019
Messages
4
Good morning!

I have a layout that looks like this:




For one criteria, I would like to:
Display QUAL if the date is in the current or previous month.
Display NC/QUAL if the date is before the previous month.
Display UNQUAL if the date is blank.

For a different criteria, I would like to:
Display QUAL if the date is from May/June/July/Aug OR if the date is in Sept/Oct/Nov/Dec
Display NC/QUAL if the date is prior to May
Display UNQUAL if the date is blank.

Thanks in advance!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Mazzic

New Member
Joined
Sep 17, 2019
Messages
4
One example for any of these would probably be enough for me to figure out the rest.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,602
Office Version
365
Platform
Windows
For the first part what happens if the date is in the future?
 

Mazzic

New Member
Joined
Sep 17, 2019
Messages
4
The date entered should never be in the future based on the way the spreadsheet works, but in a formula case QUAL would be acceptable
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,602
Office Version
365
Platform
Windows
In that case how about
=IF(A2="","UN",IF(MONTH(TODAY())-MONTH(A2)>=2,"NC/",""))&"QUAL"
 

Mazzic

New Member
Joined
Sep 17, 2019
Messages
4
That works perfectly - truly impressed with your solution!

Looking to decipher your formula so I can re-arrange it based on the other parts of my spreadsheet, if the date range is blank, it returns UN. if the month of the date is either today's month or today's month minus two months, it returns NC/, and it always returns QUAL. Right?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,075
Office Version
365
Platform
Windows
I would recommend using the DATEDIF function (https://exceljet.net/excel-functions/excel-datedif-function) instead of MONTH in case your dates aren't in the same year.
For example, let's say we were in January, and looking back at November or December dates. Substracting MONTHs will result in negative values.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,602
Office Version
365
Platform
Windows
That works perfectly - truly impressed with your solution!

Looking to decipher your formula so I can re-arrange it based on the other parts of my spreadsheet, if the date range is blank, it returns UN. if the month of the date is either today's month or today's month minus two months, it returns NC/, and it always returns QUAL. Right?
You've understood it correctly. :)
Although as Joe4 has pointed out, I didn't factor in changing years.
 

Forum statistics

Threads
1,089,452
Messages
5,408,312
Members
403,196
Latest member
annph

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top