 # IF formula with dates and rates - complex! [Closed]

Posts
1
Registration date
Thursday December 28, 2017
Status
Member
Last seen
December 28, 2017
-
Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
-
Hello,

i'm trying to get a very complex IF formula with many dates and outcomes.

if an employee's starting date is 18 Oct 2017 (cell A5), then the rate for the month should be only for 18 days at 1.67
my formula is (date 01/OCT/2017 is on cell A8)

=if(year(A5)=year(2018),if(month(A5)>month(A8),0,IF(MONTH(A5)=MONTH(A8), ((31-DAY(A5)+1)*1.67/31),IF(MONTH(A5)<MONTH(A8),1.67," ")

but i need to continue the calculations for previous years where when a staff turns 5 years in organization gets a rate of 1.75 instead of 1.67,

my calculation is:

=if(year(A5)=year(2012),1.75," ")

also for year 2013 - where calculations need to be done:

=IF(YEAR(A5)>YEAR(2013),IF(MONTH(A5)>MONTH(A8),1.67,IF(MONTH(A5)=MONTH(A8),(((31-DAY(A5)+1)*1.75/31)+(DAY(A5)*1.667/31)),IF(MONTH(A5)<MONTH(A8),1.75," ")

Combining them together, and adding the in between (2014-2017) gives me a FALSE result.

how can i do this calculation??

Posts
1837
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 17, 2020
136
samasteph, Good afternoon.

I did an example for you.
https://www.sendspace.com/file/2306j5

Please, tell us if it is what you want.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Recommended

DON'T MISS