Formula based on dates

[anum]

Hi there,

I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...

Thanks!
Anu

 

[anum]

Hi Chris, sorry i've got another complication.... is it possible to incorporate that when there is no start date, the sal for each month should be blank and only come in if the start date was entered?

 

[anum]

Ooh! think I cracked it myself!

 

[Chris Mack]

 

[anum]

Hi again! I've got another complication which I need to add to the same cell as previous formula which is to include recruitment costs.... so i used this but doesn't seem to work and gives me #VALUE!

=IF(AND($R8>0,W$2>=$R8),$O8/12,"")+IF(AND(N8>0,R8>1/8/2014),N8,"") so basically if the person is starting anytime in aug, I want the cost to come a month in advance or even maybe the month... where am i going wrong with the formula?

N8 = Recruitment costs, R8 is the start date

Once again, thanks a lot for your help

 

[Chris Mack]

Try changing "1/8/2014" to "DATE(2014,8,1)". At the moment it's carrying out division.

Chris

 

[anum]

Nope still gives me #VALUE! although if I use the formula on it's own not in conjunction with the other one, then it seems to work.. Can I not use both of them together?

 

[Chris Mack]

What's the full formula?

 

[anum]

=if(and($r8>0,w$2>=$r8),$o8/12,"")+if(and(n8>0,r8>1/8/2014),n8,"")

 

[Chris Mack]

Have you tried 'Formulas' | 'Evaluate Formula'?

Where does it go wrong?

 

[anum]

Right I figured it likes to pull back zero rather than "" Now the issue I have is if the start is 01/9/2014 and in Aug if I type in start date greater than 01/08/2014, then it gives me the recruitment fee again in Aug. Can I say if>01/8/14 AND <01/9/14??