Date Drop Down

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,175
Office Version
  1. 2016
Platform
  1. Windows
Hi JWGreen1986,

Is this the type of thing you wanted?

Book1
ABCDEF
1Sales Reps.Start DateSelectEOM Dates
2John1/1/202029-Feb-2031-Jan-20
3Alf10/1/201929-Feb-20
4Vanessa4/4/202031-Mar-20
5Susan11/11/201930-Apr-20
6John6/6/202031-May-20
7Sarah6/3/201930-Jun-20
831-Jul-20
931-Aug-20
1030-Sep-20
1131-Oct-20
1230-Nov-20
1331-Dec-20
1413-Mar-20
Sheet1
Cell Formulas
RangeFormula
F14F14=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A17Expression=AND(A2<>"",EDATE(EOMONTH($D$2,0),-3)<B2)textNO
Cells with Data Validation
CellAllowCriteria
D2List=$F$2:$F$14
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,914
Members
412,689
Latest member
nhsmedic
Top