Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-18-2022, 01:30 AM
stu_c stu_c is offline Date Conditional Formatting Windows 10 Date Conditional Formatting Office 2016
Novice
Date Conditional Formatting
 
Join Date: Dec 2013
Posts: 12
stu_c is on a distinguished road
Default Date Conditional Formatting

Hi all,


I want to be able to do a spreadsheet for peoples training, some training valid for longer times which is shown in the file attached.

what I want to be able to do is add the amount of years valid for onto the last date date taken and then show either show the following
White - 1 Year left or more
Green - 6 Months Left
Orange - 3 Months Left
Red - 1 Month Or Less

I was thinking of having another column showing the date due (Date Taken + Row 2 in years) then hide that column and do the conditional formatting from that but didn't know if there was a better way?

Problem I have is I don't know the formula to add the row 2 onto the years and also in conditional formatting how you would do for example (B4 + B2 = <Today()

I do not know if I am just over thinking it, been a very long time since doing Excel formulas :/
Attached Files
File Type: xlsx Training.xlsx (9.9 KB, 6 views)
Reply With Quote
  #2  
Old 05-18-2022, 01:50 AM
ArviLaanemets ArviLaanemets is offline Date Conditional Formatting Windows 8 Date Conditional Formatting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Here you go!

I set the Conditional Format for overtime, you can easily add formats for various warnings (adding/removing the number of years/months in DATE() function in formula),
Attached Files
File Type: xlsx Training.xlsx (10.4 KB, 6 views)
Reply With Quote
  #3  
Old 05-18-2022, 02:04 AM
stu_c stu_c is offline Date Conditional Formatting Windows 10 Date Conditional Formatting Office 2016
Novice
Date Conditional Formatting
 
Join Date: Dec 2013
Posts: 12
stu_c is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Here you go!

I set the Conditional Format for overtime, you can easily add formats for various warnings (adding/removing the number of years/months in DATE() function in formula),
thank you!, I am guessing if I wanted a conditional format after 6 months would it be
=DATE(YEAR(B4) + B$2, MONTH(B4), DAY(B4))<TODAY()-182
Reply With Quote
  #4  
Old 05-18-2022, 04:11 AM
ArviLaanemets ArviLaanemets is offline Date Conditional Formatting Windows 8 Date Conditional Formatting Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by stu_c View Post
I am guessing if I wanted a conditional format after 6 months would it be
=DATE(YEAR(B4) + B$2, MONTH(B4), DAY(B4))<TODAY()-182
OR
the 4 conditional format formulas may be
=(DATE(YEAR(B4) + B$2, MONTH(B4)-12, DAY(B4))<TODAY())
=(DATE(YEAR(B4) + B$2, MONTH(B4)-6, DAY(B4))<TODAY())
=(DATE(YEAR(B4) + B$2, MONTH(B4)-3, DAY(B4))<TODAY())
=(DATE(YEAR(B4) + B$2, MONTH(B4)-1, DAY(B4))<TODAY())
(And you have to combine the order of formulas, and StopIfTrue, to get right colors used)
Reply With Quote
  #5  
Old 05-22-2022, 12:39 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Date Conditional Formatting Windows 7 64bit Date Conditional Formatting Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

FWIW, this part
TODAY()-182
may not return the correct date

Better use EDATE(TODAY(),-6)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting a row based on date cwkotch Excel 1 08-28-2021 10:14 PM
Conditional Formatting based on date teza2k06 Excel 2 11-27-2018 09:55 AM
Date Conditional Formatting Conditional Formatting based on dynamic Date Modifier1000 Excel 2 09-12-2014 08:53 AM
Date Conditional Formatting Date Conditional Formatting teza2k06 Excel 1 08-09-2014 09:58 AM
Date Conditional Formatting Conditional formatting question based on cell date Cosmo Excel 2 04-08-2013 12:12 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft