Getting Tax Period from current date

[crxftw]

Hello. I need to show Tax Period on its field depending on Pay Date. Tax period starts from 1st April so periods go like that: 1 - April, 2 - May, 3 - June etc. For example if Pay date is 31/07/2011 then Tax Period for this should be shown as 03/2011 as June is 3rd period.

Should be pretty easy but I can't figure it out myself. Thanks

 

[ted.martin]

One way would be to create a table with 12 records relating one to each Tax Period with second and third fields being the start and end dates for the period. Then use a DLOOKUP of the Tax Period against your objective date between the start and end dates.

 

[crxftw]

I figured it out like that:

="Month: " & (Month([txtPayDate])+IIf(Day([txtPayDate])<1,4,9)-1) Mod 12+1 & " (" & Year([txtPayDate])-IIf([txtPayDate]<DateSerial(Year([txtPayDate]),4,1),1,0) & ")"