IF an increase post 1/7/2021 & increase in FTE Salary - what is the difference?

[Kelli Webb]

Hi, sorry if this is a duplicate post from last night - I couldn't find it..

I would like some assistance to identify employees in the attached who has received an increase after the 1/7/2021 & if so, how much was it?

As per the example in columns E & F.

thank you for your time! Kelli

Employee Id	First Name	Effective Date	FTE Salary	YTD Increase	Increase Amount
99478​	Employee 1	7/07/2020​	166000​	No
99495​	Employee 2	1/07/2020​	53000​	No
99497​	Employee 3	25/02/2020​	194310​	No
99497​	Employee 3	1/07/2020​	196000​	No
99501​	Employee 4	1/07/2020​	60000​	No
99501​	Employee 4	12/09/2020​	72170​	Yes	12170​
99507​	Employee 5	1/07/2020​	239292​	No
99508​	Employee 6	1/07/2020​	127000​	No
99511​	Employee 7	1/07/2020​	140000​	No
99511​	Employee 7	10/10/2020​	141000​	Yes	1000​
99532​	Employee 15	1/07/2019​	71500​	No
99532​	Employee 15	12/09/2020​	71500​	No
99532​	Employee 16	15/09/2020​	72000​	Yes	500​

 

[Kelli Webb]

If anyone could help, that would be great I'm desperate!!

 

[vcoolio]

Hello Kelli,

I don't see anything for 2021 in your sample but I'd assume that you are actually referring to 2020 perhaps?

If so, have you considered using the Advanced Filter?

Cheerio,
vcoolio.

 

[vcoolio]

...................here's a method using Autofilter with the relevant rows of data then high-lighted in yellow:-

Sub Test()

Application.ScreenUpdating = False

        With Sheet1.[A1].CurrentRegion
                .Columns("A:F").Offset(1).Interior.ColorIndex = xlNone
                .AutoFilter 3, ">=" & "1/7/2020"
                .AutoFilter 5, "Yes"
                .Columns("A:F").Offset(1).Resize(.Rows.Count - 1).Interior.ColorIndex = 6
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Kelli Webb]

Hello Kelli,

I don't see anything for 2021 in your sample but I'd assume that you are actually referring to 2020 perhaps?

If so, have you considered using the Advanced Filter?

Cheerio,
vcoolio.

Hi Vcoolio, sorry it should be 2020 i.e.greater than 1/7/2020. I was rushing to get to the hospital.

I need to pick up just those with an increase, we have PT employees who change their hours regularly, but there FTE doesn't change so I only want to capture those who have.
Kelli

 

[vcoolio]

No worries Kelli. I hope all is well.

Did you have a look at post #4?

Cheerio,
vcoolio.

 

[Peter Bartholomew]

A 365 solution using Tables

= LET(
  QualifyingDate?, [@[Effective Date]]>DATE(2020,7,1),
  PriorSalary, XLOOKUP([@[Employee Id]], [Employee Id], [FTE Salary]),
  CurrentSalary, [@[FTE Salary]],
  IF(QualifyingDate?, CurrentSalary - PriorSalary, 0) )