I need to highlight a cell when a date is 2 and 1 motths away from being half a year

[Schtrudel]

Hello everyone!

So I work in a company where we sometimes employ people on their Work and Travel visas. In Australia it means that they can't work for one employer for more than 6 months so I would like to track that. Currently I use this formula to track the length of service:

Please Login or Register  to view this content.

which gives me:

0 years 3 months 9 days

What I would like to add is: automatically highlight the cell with the name in yellow when there's 2 months left until 6 months of service and in red when 1 month or less left till 6 months of service and that should happen only if this person has WT (work and travel) in their Notes column. Currently I can't figure it out. Could you help me with that please?

[ELeGault]

It will be the same formula essentially. Basically you will highlight the range of names, then put in the formula that calculates the condition you wish to have it set under. Make sure you are basing it on the first persons date or first date/header relative to the range you selected. Example - If I was to have names down Col A and Dates Started in B and Date Expires in C then I would highlight the range of names lets say $A$1:$A$100 and then go to conditional formatting and add a formula based condition. This formula would then look something like =($C1-Today())<=30 when this is true apply the formatting I select. This is to say if the date in C1 is less than or equal to today apply format. Now take note that I am not locking the row as I want the CF to be relative to repeat this logic down all of C applying formatting to my selected Range of A1 - A100. If you are still having issues post a sample file and I or someone here will be able to elaborate.

[bebo021999]

With C16 contains "WT"

C18 is start date

=>EDATE(C18,6) is date of 6 months later

and months counted from today:
=DATEDIF(TODAY(),EDATE($C$18,6),"YM")

with WT criteria:
=(C16="WT")*DATEDIF(TODAY(),EDATE($C$18,6),"YM")=2

choose format name as yellow.

[knitterkuba]

Alternative solution using the one column with your current formula):
Using conditional formatting (entire column):
=IF(AND(VALUE(TRIM(MID(A1,8,3)))<=0,B1="WT",(VALUE(LEFT(A1,1)))=0),"TRUE","FALSE") [set fill to red]
=IF(AND(VALUE(TRIM(MID(A1,8,3)))<=1,B1="WT",(VALUE(LEFT(A1,1)))=0),"TRUE","FALSE") [set fill to yellow]

*Replace A1 and B1 with the applicable cells
**If it is exactly 2 months then it will still be white and if it is exactly 1 month it will still be yellow (you can add an AND statement to fix that)