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 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 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??
Related:

1 response

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Dec 28, 2017 at 12:08 PM
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
0