# Excel - IF Function Problem - Expert Solution

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
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
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
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
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
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
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
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
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
Excelchat Expert 31/08/2018 - 05:20
Excelchat Expert 31/08/2018 - 05:20
can you check this
Excelchat Expert 31/08/2018 - 05:21
same 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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.