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

 

[Chris Mack]

and(a1>date(2014,8,1),a1<date(2014,9,1))

 

[anum]

Oh you genius! Thanks for this!

 

[anum]

Ahh actually that didnt work lol this is what I've got now. I thought it worked but after opening file again after couple of hours it didnt!

=IF(AND($S30>0,Y$2>=$S30),$P30/12,"0")+IF(AND(O30>0,S30>1/9/2014,S30<1/10/2014),O30,"0")

S30 = start date; Y2 = Month in the financial year; P30 = Annual salary; O30 = Recruitment Fees


UPDATE: I used your DATE(...) thing and it worked!! Man! glad it's Friday tomorrow!!

 

[Chris Mack]

​UPDATE: I used your DATE(...) thing and it worked!! Man! glad it's Friday tomorrow!!