**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need the formula for time accrual to auto populate in E4 based on certain criteria.
example:
B4 = hire date
E4= accrual rate
If todays date is equal to or greater than 60 days but less than 1 year from B4 date then E4 = 1
If todays date is equal to or greater than 3 years but less than 4 years from B4 date then E4 = 1.08
If todays date is equal to or greater than 5 years but less than 9 years from B4 date then E4 = 1.5
If todays date is equal to or greater than 10 years but less than 14 years from B4 date then E4 = 1.58
If todays date is equal to or greater than 15 years from B4 date then E4 = 1.92

Solved by A. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
31/08/2018 - 04:42

Hi

User
31/08/2018 - 04:42

hello, did my issue make sense?

Excelchat Expert
31/08/2018 - 04:42

yes but can you provide me with few sample rows so i can build the formula

User
31/08/2018 - 04:43

what do you mean sample rows

User
31/08/2018 - 04:43

from my spread sheet?

Excelchat Expert
31/08/2018 - 04:43

yes

Excelchat Expert
31/08/2018 - 04:43

like the format of the data and.. so just sample rows for me to work on the formula

Excelchat Expert
31/08/2018 - 04:44

will you hqave today date in b3 in your spreadsheet?

Excelchat Expert
31/08/2018 - 04:44

ok

User
31/08/2018 - 04:44

there you go

Excelchat Expert
31/08/2018 - 04:44

so b1 is todays date?

User
31/08/2018 - 04:45

yes man auto populates to todays date

Excelchat Expert
31/08/2018 - 04:45

and you need formula for E right

Excelchat Expert
31/08/2018 - 04:45

give me few min to work on the formula

User
31/08/2018 - 04:45

yes based on the criteria I listed. when I enteee the hire date, I need the accrual rate auto populatee

Excelchat Expert
31/08/2018 - 04:45

sure

Excelchat Expert
31/08/2018 - 04:45

got it

User
31/08/2018 - 04:46

thank you

Excelchat Expert
31/08/2018 - 04:46

all the criterias are listed in the descrition right

User
31/08/2018 - 04:46

yes

Excelchat Expert
31/08/2018 - 04:46

ok few min please

Excelchat Expert
31/08/2018 - 04:47

do you have =TODAY() in B1?

User
31/08/2018 - 04:47

yes

Excelchat Expert
31/08/2018 - 04:47

ok

Excelchat Expert
31/08/2018 - 04:48

b <60 then e=0 i assume

User
31/08/2018 - 04:49

yes

Excelchat Expert
31/08/2018 - 04:51

what should the value be for 1-3

Excelchat Expert
31/08/2018 - 04:51

If todays date is equal to or greater than 60 days but less than 1 year from B4 date then E4 = 1
If todays date is equal to or greater than 3 years but less than 4 years from B4 date then E4 = 1.08

Excelchat Expert
31/08/2018 - 04:52

so less than 1 yr then e=1

Excelchat Expert
31/08/2018 - 04:52

greater then 3 then e=1.08

Excelchat Expert
31/08/2018 - 04:52

what about inbetween

User
31/08/2018 - 04:52

from 1-2 years it will also be 1

Excelchat Expert
31/08/2018 - 04:53

so less than 3 yrs is also 1

User
31/08/2018 - 04:53

yes

Excelchat Expert
31/08/2018 - 05:00

still working on the formula fe min

User
31/08/2018 - 05:00

thank you

Excelchat Expert
31/08/2018 - 05:02

[Uploaded an Excel file]

Excelchat Expert
31/08/2018 - 05:03

the formula in E is an array formula

Excelchat Expert
31/08/2018 - 05:03

60days < b < 3 year e =1
3yrs < b < 5 yrs e=1.08
5yrs< b < 10 yrs e=1.5
10 yrs < b <15yrs e=1.58
15yrs < b e=1.92

Excelchat Expert
31/08/2018 - 05:03

this is the criterias as there were some missing year like 1-3 which you clarified to fall in the previous bucket

Excelchat Expert
31/08/2018 - 05:04

copy sheet 2 into you excel

Excelchat Expert
31/08/2018 - 05:04

then copy paste the formula in you sheet .make sure the sheet name is sheet2 or if you rename the sheet please change the name int he formula as well

Excelchat Expert
31/08/2018 - 05:04

and press cntrl+shift+enter

Excelchat Expert
31/08/2018 - 05:05

please do verify the solution

User
31/08/2018 - 05:08

it is returning a value of 0

User
31/08/2018 - 05:08

for all of them

User
31/08/2018 - 05:10

is this the formula I am pasting into E

User
31/08/2018 - 05:10

=IFERROR(INDEX(Sheet2!$C$2:$C$6,MATCH(1,(($B$1-$B4)>=Sheet2!$A$2:$A$6)*(($B$1-$B4)<=Sheet2!$B$2:$B$6),0)),0)

Excelchat Expert
31/08/2018 - 05:10

can you send a screen shot highlighting the cell with the formula

Excelchat Expert
31/08/2018 - 05:10

thne cntrl+shift+enter

Excelchat Expert
31/08/2018 - 05:12

you there?

Excelchat Expert
31/08/2018 - 05:12

is it working

User
31/08/2018 - 05:12

yes im trying to get to work

User
31/08/2018 - 05:12

no

Excelchat Expert
31/08/2018 - 05:12

ok try this

Excelchat Expert
31/08/2018 - 05:12

=IF(COUNTIF(Sheet2!A2:A6,"<"&B1-B4)=0,0,INDEX(Sheet2!C2:C6,COUNTIF(Sheet2!A2:A6,"<"&B1-B4)))

Excelchat Expert
31/08/2018 - 05:12

its a regular formula

Excelchat Expert
31/08/2018 - 05:12

paste it in e4 then click enter

User
31/08/2018 - 05:13

I did

User
31/08/2018 - 05:13

it shows 0

Excelchat Expert
31/08/2018 - 05:13

ok can you send a screen shot

Excelchat Expert
31/08/2018 - 05:14

can you share the excel your working on?

User
31/08/2018 - 05:14

sending

[Uploaded an Excel file]

User
31/08/2018 - 05:15

yes how do I share it

Excelchat Expert
31/08/2018 - 05:15

there is a paper clip symbol just attach here

Excelchat Expert
31/08/2018 - 05:15

can you use this instead

Excelchat Expert
31/08/2018 - 05:15

=IF(COUNTIF(Sheet2!A2:A6,"<"&B1-B4)=0,0,INDEX(Sheet2!C2:C6,COUNTIF(Sheet2!A2:A6,"<"&B1-B4)))

User
31/08/2018 - 05:16

[Uploaded an Excel file]

Excelchat Expert
31/08/2018 - 05:20

[Uploaded an Excel file]

Excelchat Expert
31/08/2018 - 05:20

can you check this

Excelchat Expert
31/08/2018 - 05:21

same file

[Uploaded an Excel file]

Excelchat Expert
31/08/2018 - 05:21

this one you can drag the formula down and the reference wil auto update

User
31/08/2018 - 05:21

yes the one field in E4 is working but I cant drag the formula down for the other employees

User
31/08/2018 - 05:21

it shows 0

Excelchat Expert
31/08/2018 - 05:22

ya can you use the 2nd file

Excelchat Expert
31/08/2018 - 05:22

=IF(COUNTIF(Sheet2!$A$2:$A$6,"<"&$B$1-$B4)=0,0,INDEX(Sheet2!$C$2:$C$6,COUNTIF(Sheet2!$A$2:$A$6,"<"&$B$1-$B4)))

Excelchat Expert
31/08/2018 - 05:22

this is the dragable formula

Excelchat Expert
31/08/2018 - 05:23

is it working

Excelchat Expert
31/08/2018 - 05:24

you there

Excelchat Expert
31/08/2018 - 05:29

Have a nice day

Excelchat Expert
31/08/2018 - 05:29

Please do leave a feedback at the end of the session

User
31/08/2018 - 05:38

its working!!

User
31/08/2018 - 05:38

thank you

Excelchat Expert
31/08/2018 - 05:39

Have a nice day

Excelchat Expert
31/08/2018 - 05:39

Please do leave a feedback at the end of the session

User
31/08/2018 - 05:39

thank you will do

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*