Tax Week Number

[Jon von der Heyden]

Hi,

I can retrieve the weeknum of any particular date using =WEEKNUM(x) or =TRUNC(((x-DATE(YEAR(x),1,0))+6)/7)

But I'm looking for a UDF to output the tax year week number where the tax year always starts on 6th April. The std weeknum for 6th of april pretty much varies around week 13 but as it varies I don't ever get a strictly true result by deducting 13 from WEEKNUM().

Anybody have any idea?

Thanks,
Jon

 

[viquarshaikh]

=TRUNC(((A1-IF(DATE(YEAR(A1),4,5)<A1,DATE(YEAR(A1),4,5),DATE(YEAR(A1)-1,4,5))+6)/7))

Hope this helps... If not we can give VBA a shot

 

[Jon von der Heyden]

Slick, I managed to work it out last night with

=IF(A16>=DATE(YEAR(A16),4,6),TRUNC((A16-DATE(YEAR(A16),4,6)+364)/7)+1-52,TRUNC((A16-DATE(YEAR(A16),4,6)+364)/7)+1)

but the one you've suggested is somewhat easier to follow!

Thanks

 

[barry houdini]

Here's another way

=INT((A1-DATE(YEAR(A1)-(TEXT(A1,"mdd")+0<406),3,30))/7)

....or with hindsight (about 17 months' worth) you could do that this way

=INT((A1-DATE(YEAR(A1+270),-8,-1))/7)

 

[MartinL]

Thanks guys just looking for this very thing