# IF formula with dates and rates - complex!

Closed
samasteph Posts 1 Registration date Thursday December 28, 2017 Status Member Last seen December 28, 2017 - Dec 28, 2017 at 09:45 AM
Mazzaropi Posts 1983 Registration date Monday August 16, 2010 Status Contributor Last seen February 27, 2023 - 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??