IF and Match and Input value Issue.

[timuk26]

Stumped myself with this one.

This one has had me for hours.

Tried 4 or 5 different formula but still cannot for the life of me get it to work.

So looking for a formula which will do the following.

2 reference dates for new staff members - and their efficiency

Start Date - Column B - rows 2 - 5
Go Live Date - Column D - rows 2-5
Todays Date - Column E - rows 2 - 5
Days Live Column F = Column E - Column D
Column G = Efficiency today based on the formula below. - rows 2 - 5
In Row 1 - cells H1 to U1 has dates from today 29/1/2025 - then the next day then the next day and so on for a month


If the person has been live for more than 47 days = 3.6 efficiency for each day which I use the formula below

=IF(F2>47, 3.6, IF(AND(F2>=0, F2<=6.99999), 1.5, IF(AND(F2>=7, F2<=13.99999), 1.85, IF(AND(F2>=14, F2<=20.99999), 2.2, IF(AND(F2>=21, F2<=27.99999), 2.55, IF(AND(F2>=28, F2<=34.99999), 2.9, IF(AND(F2>=35, F2<=41.99999), 2.9, IF(AND(F2>=42, F2<=48.99999), 3.25, "") ) ) ) ) ) ) )

However if the go Live date is in the future

I want a formula to look up when they go live in column D, and match and check if the date is in cells in Row 1, cells H1 to U1, then when the date matches (exactly) enter 1.5 in that cell, in row 2, then and this is where im struggling, for the next day 0.35 to that, then the following day another 0.35 until 3.6 is reached, and once 3.6 is reached leave it as 3.6 ongoing.

So 1.5, 1.85, 2.25. 2.6. 2.9. 3.25, 3.6 and then 3.6 ongoing.

This one has had me for hours.

Very Grateful for any help.

Tim

 

[Sergius]

Hello! A small example will speed up help.

 

[timuk26]

Example screenshot attached thanks