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.
