IF formula with dates and rates - complex!

samasteph 1 Posts Thursday December 28, 2017Registration date December 28, 2017 Last seen - Dec 28, 2017 at 09:45 AM - Latest reply: Mazzaropi 1697 Posts Monday August 16, 2010Registration dateContributorStatus January 20, 2018 Last seen
- Dec 28, 2017 at 12:08 PM
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??
See more 

1 reply

Reply to this topic
Mazzaropi 1697 Posts Monday August 16, 2010Registration dateContributorStatus January 20, 2018 Last seen - Dec 28, 2017 at 12:08 PM
0
Helpful
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
Respond to Mazzaropi