Date Drop Down

[jwgreen1986]

I have built a spreadsheet that contains a league table for sale execs. the main table page has various drop downs to change the criteria of the tables.

I have a date drop down that lists the months (Jan-Dec) and also has a YTD option to show the sales for YTD.

the thing i am wanting to do now is highlight any staff that will be classed as a new starter. to be declared a new starter you need to be working for up to 3 months before a certain date.

So what i want to do is if i select January for the drop down, it will look at the sale execs start dates and if their start date is up to three months before this it will highlight. again with Feb look at their start dates, if it was 3 months before this then highlight them and so on.

i have the employee start dates so this isnt hard to include in the tables. I also have a seperate worksheet that has the start dates and end dates of a month, with the month being at the beginning of it. so it isnt hard to use this either.

the problem i am having is creating the formula for it. i know in my head what i need, but cant seem to put it into a working formula.

so what i want is =if (data drop down is jan) then look at a persons start date and if this is up to 3 months less than the end date of the selected month then highlight else leave as it is.
i thought it would also be handy to try and link the YTD figure to the live date as well. so if the ytd is selected in the drop down it will take todays date and work out if a sale exec is a new starter based on todays date, so 3 months before todays date.

if anyone can help that would be fantastic. as i have mentioned this has really got me stumped.

 

[Toadstool]

Hi JWGreen1986,

Is this the type of thing you wanted?

Book1
	A	B	C	D	E	F
1	Sales Reps.	Start Date		Select		EOM Dates
2	John	1/1/2020		29-Feb-20		31-Jan-20
3	Alf	10/1/2019				29-Feb-20
4	Vanessa	4/4/2020				31-Mar-20
5	Susan	11/11/2019				30-Apr-20
6	John	6/6/2020				31-May-20
7	Sarah	6/3/2019				30-Jun-20
8						31-Jul-20
9						31-Aug-20
10						30-Sep-20
11						31-Oct-20
12						30-Nov-20
13						31-Dec-20
14						13-Mar-20
Sheet1

Cell Formulas
Range	Formula
F14	F14	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A17	Expression	=AND(A2<>"",EDATE(EOMONTH($D$2,0),-3)<B2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$F$2:$F$14