Find number of tax / fiscal years between two dates

[Paul_s_Moody]

Hi,

I'm working with a client and need to determine how many tax years an individual was a member part of a pension scheme.

In the UK tax years run from 6 April to 5 April...

So for example if the member joined the scheme on 31/7/2007 and left of 23/10/2011 then:

* The start of the 1st tax year was 6/4/2008

* The end of their last tax year was 5/4/2011

* So this individual was a member for 3 full tax years.

If, however, the individual had joined on 31/3/2007 then they would have been a member for 4 full tax years.

I suspect it's quite a simple formula to construct - I'm just struggling to find it!

Many thanks,

[Christina_H · Accepted solution]

I think this does what you need:

DateTimeYear([LeftDate])-DateTimeYear([JoinedDate])
- IF [JoinedDate]<ToDate(ToString(DateTimeYear([JoinedDate]))+"-04-06") THEN 0 ELSE 1 ENDIF
- IF [LeftDate]<ToDate(ToString(DateTimeYear([LeftDate]))+"-04-06") THEN 1 ELSE 0 ENDIF

[Paul_s_Moody]

Hi,

Thank you so very much!

I had to make one small tweak (bold highlighted) but this now works perfectly.

DateTimeYear([LeftDate])-DateTimeYear([JoinedDate])
- IF [JoinedDate]<=ToDate(ToString(DateTimeYear([JoinedDate]))+"-04-06") THEN 0 ELSE 1 ENDIF
- IF [LeftDate]<ToDate(ToString(DateTimeYear([LeftDate]))+"-04-06") THEN 1 ELSE 0 ENDIF

I'd managed to get close, but my formula had many more clauses to try and adjust the start/end dates to the relevant tax year start before doing the differencing.

Your's is a much cleaner solution.

[Christina_H]

I knew I would have missed something 🙂