I dont understand. You stated that your year field is a date field. What date is in it?
hi sorry for not being clear
the year field has values
I think your "convert" calculation isn't set up right.
If (Left(month;3)="Jan"; "1/1";
seems to leave the result as text instead of a number.
Case (Month = "january" ; 1 ; Month = "February"; 2 ; Month = "March" ; 3 ....)
Would convert your month text into a number.
Your calculation for 2 months prior would be:
Date ( TextToMonth -2 ; 1 ; YearField )
Dates in January and February will correctly compute to a date showing the previous year.
many thanks for this - I will try it first thing tomorrow at work
absolutely brilliant - worked like a dream - thanks so much!!