Calculated Date Problem

[Haynesey]

I use the following bit of code to find the difference in months between the Date of invoice of an item and the beginning of the next financial year.

DateDiff("m",[DepreciationInvoiceDate],IIf(Format$(Date(),"mm")>4,"01/04/" & Format$(Date(),"yy"),"01/04/" & Format$(Date(),"yyyy")

This works great, however now I have been asked to change it so that it says the difference in months between the first of the financial year (01 April) previous to the Date of invoice and the beginning of the next financial year. I have been given a deadline of the end of today to get this working.

Please help.

Thanks in advance

Lee

 

[andrew93]

Hi Lee

Try this : DateDiff("m","01/04/" & (Year([DepreciationInvoiceDate])-IIf(Month([DepreciationInvoiceDate])>3,0,1)),"01/04/" & (Year(Date())+IIf(Month(Date())>3,1,0)))

HTH, Andrew

 

[Haynesey]

Hi

Thanks, this worked great.

Cheers

Lee