1

This is my first question using StackExchange, so please bear with me. I am making a spreadsheet for tracking due dates for training. Sheet 1 shows due dates that people with view only capabilities can see. Sheet 1 has all of the conditional formatting and formulas referencing all information to Sheet 2. Sheet 2 shows the last dates that the training was done, which will only be viewable by editors of the spreadsheet.

Quick Rundown:

  • Training A is good for 2 years.

  • Training B-E has to be done between April 1st and March 31st cycles, so if training is within those dates, then the next due date will show March 31st of the next year.

  • Training F is good for 1 year.

  • Training G is good until December 31st of the following year

  • Training H-J is semi-annually, so if it was done anytime within the first quarter of the calendar year, next due date is the last day of the third quarter. If it was done anytime within the second quarter, due date is the last date of the fourth quarter, etc.

  • Training K-M is good for 1 year.

  • Training N-P is good for 2 years.

  • Training Q-Z is one-time training, so no due dates and is just referenced to the cell on Sheet 2.

So, the problem I'm having is with the Conditional Formatting.I originally had one set of formatting for the entire sheet, but when I noticed certain cells weren't highlighting the way they were supposed to, then I broke up the conditional formatting into groups.

Expected Results

  • highlight yellow if date is between 4-6 months from expiring

  • highlight orange if date is between 2-4 months from expiring

  • highlight red if date is between 0-2 months from expiring

  • highlight black with red letters if date is expired

  • highlight cyan if date is today

Actual Result

  • No highlighting in columns D-G and I-L

Is there anything that is preventing this from happening? Should I be using any different formulas for the results needed?

Here is the link: https://docs.google.com/spreadsheets/d/1gMC6vuCfjPuDMHpGw43sRheYxCqi09xGqBqW155gUjA/edit?usp=sharing

  • can you highlight the expected output? – user0 Jun 9 at 15:26
  • I updated with Expected Results and Actual Result. Is that what you are referring to? – RAY BISS Jun 9 at 18:44
0

Formulas you have in D:G and I:L do not return valid dates but text strings so Conditional formatting for dates does not pick it up. To fix it you need to wrap your formulas into

=TO_DATE(IFERROR(DATEVALUE(_formula_here_)))

You can also level up your sheet skils with deleting column J (range: '1'!J5:J) and paste this into J5 cell:

=ARRAYFORMULA(TO_DATE(IFERROR(DATEVALUE(IF(LEN('2'!K5:K), 
 IF(('2'!K5:K > DATE(2017,  1, 1)) * ('2'!K5:K < DATE(2017,  3, 31)), "09/30/2017", 
 IF(('2'!K5:K > DATE(2017,  4, 1)) * ('2'!K5:K < DATE(2017,  6, 30)), "12/31/2017", 
 IF(('2'!K5:K > DATE(2017,  7, 1)) * ('2'!K5:K < DATE(2017,  9, 30)), "03/31/2018", 
 IF(('2'!K5:K > DATE(2017, 10, 1)) * ('2'!K5:K < DATE(2017, 12, 31)), "06/30/2018", 
 IF(('2'!K5:K > DATE(2018,  1, 1)) * ('2'!K5:K < DATE(2018,  3, 31)), "09/30/2018", 
 IF(('2'!K5:K > DATE(2018,  4, 1)) * ('2'!K5:K < DATE(2018,  6, 30)), "12/31/2018", 
 IF(('2'!K5:K > DATE(2018,  7, 1)) * ('2'!K5:K < DATE(2018,  9, 30)), "03/31/2019", 
 IF(('2'!K5:K > DATE(2018, 10, 1)) * ('2'!K5:K < DATE(2018, 12, 31)), "06/30/2019", 
 IF(('2'!K5:K > DATE(2019,  1, 1)) * ('2'!K5:K < DATE(2019,  3, 31)), "09/30/2019", 
 IF(('2'!K5:K > DATE(2019,  4, 1)) * ('2'!K5:K < DATE(2019,  6, 30)), "12/31/2019", 
 IF(('2'!K5:K > DATE(2019,  7, 1)) * ('2'!K5:K < DATE(2019,  9, 30)), "03/31/2020", 
 IF(('2'!K5:K > DATE(2019, 10, 1)) * ('2'!K5:K < DATE(2019, 12, 31)), "06/30/2020", 
 IF(('2'!K5:K > DATE(2020,  1, 1)) * ('2'!K5:K < DATE(2020,  3, 31)), "09/30/2020", 
 IF(('2'!K5:K > DATE(2020,  4, 1)) * ('2'!K5:K < DATE(2020,  6, 30)), "12/31/2020", 
 IF(('2'!K5:K > DATE(2020,  7, 1)) * ('2'!K5:K < DATE(2020,  9, 30)), "03/31/2021", 
 IF(('2'!K5:K > DATE(2020, 10, 1)) * ('2'!K5:K < DATE(2020, 12, 31)), "06/30/2021",
 )))))))))))))))), )))))

0

  • 1
    Thank you, user0, that formula worked perfectly for the intended purposes! I was able to use it the way you said, as well as slightly modify it to work in other columns. Thanks again! – RAY BISS Jun 10 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.