Microsoft Office Forums Conditional Formatting date change "Y"

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-24-2019, 07:14 AM
lolla70 lolla70 is offline Conditional Formatting date change "Y" Windows 10 Conditional Formatting date change "Y" Office 2016
Novice
Conditional Formatting date change "Y"
 
Join Date: Mar 2019
Posts: 10
lolla70 is on a distinguished road
Default Conditional Formatting date change "Y"

I have Three columns



G is Total Years of Experience Formula is =DATEDIF(H2,$M$5,"Y")+I2
H is hire Date - Just date
I is Prior Years of Experience - Just Date
and M is Today Date =TODAY()

I want to do a conditional formatting that every time a number change in G column to highlight that cell

Thanks in advance!
Attached Images
File Type: jpg date.JPG (23.3 KB, 14 views)
Reply With Quote
  #2  
Old 04-24-2019, 09:14 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formatting date change "Y" Windows 7 64bit Conditional Formatting date change "Y" Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Please post a sample sheet with some data and eventually expected results ( no pics please they are useless). thx
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 04-24-2019, 10:02 AM
lolla70 lolla70 is offline Conditional Formatting date change "Y" Windows 10 Conditional Formatting date change "Y" Office 2016
Novice
Conditional Formatting date change "Y"
 
Join Date: Mar 2019
Posts: 10
lolla70 is on a distinguished road
Default

Here is a sample
Attached Files
File Type: xlsx Test Excel Date.xlsx (13.8 KB, 4 views)
Reply With Quote
  #4  
Old 04-24-2019, 11:13 AM
p45cal p45cal is offline Conditional Formatting date change "Y" Windows 10 Conditional Formatting date change "Y" Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by lolla70 View Post
I want to do a conditional formatting that every time a number change in G column to highlight that cell
You're going to struggle with that one because you haven't said how long you want the highlighting to show. From the formula I guess each cell in column G is going to change once a year, so how long after that change has happened do you want to stop highlighting?


You could conditionally highlight those cells which have changed since a certain date of your choosing; in the attached I've put such a date in cell K1 and conditional formatting in column G. Change the date manually in K1 and see different cells being highlighted yellow.


You might want to highlight those cells which have changed in the last 90 days, in which case instead of manually adjusting cell K1, put a formula in there such as:
=TODAY()-90


Of course, if you're sure you'll always want only to see changes in the last ninety days, you can incorporate that into the conditional formatting formula:
=(DATEDIF($H2,TODAY()-90,"Y")+I2)<>$G2
and dispense with the K1 cell.


In column N, I've put the formulae I used for conditional formatting. It's just for your information, they should be deleted since they're not needed.



Note that none of this will highlight changes due to a change in column H and/or I, only changes due to the passage of time. You'll need VBA (macros) to do that.
Attached Files
File Type: xlsx msofficeForums42335Test Excel Date.xlsx (14.2 KB, 3 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N mikey386 Excel 2 12-11-2014 01:14 PM
Conditional Formatting date change &quot;Y&quot; Numbering format not "tracked" in track changes w/ formatting "on." Velocitydreamer Word 1 05-26-2013 07:04 AM
Conditional formatting - "From" - Why does it recognise only names and not emails ghumdinger Outlook 0 09-18-2011 01:28 AM
How do I change the "and" to "or" with multiple Rules (and Alerts)? bschimmel Outlook 0 11-16-2009 05:26 AM
Conditional Formatting date change &quot;Y&quot; I need help with "Conditional Formatting" tgorrie Excel 1 02-28-2006 12:18 PM


All times are GMT -7. The time now is 05:11 AM.


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