Week Numbers for Tax Year

[mlv]

Hi

Can anyone post a reliable formula for calculating week numbers in Excel
that relate to the UK Tax year?

I guess week 1 would be the week with 6th April in it, or maybe week 1 is
the first full week after 5th April. Does anyone know?

Thanks

 

[Bob I]

Maybe check with the UK tax folks to find out the criteria?

 

[Rick Rothstein \(MVP - VB\)]

Can anyone post a reliable formula for calculating week numbers in Excel

that relate to the UK Tax year?

I guess week 1 would be the week with 6th April in it, or maybe week 1 is
the first full week after 5th April. Does anyone know?

Using the code at this site

http://www.merlyn.demon.co.uk/weekcalc.htm#UKTW

and, hopefully translating it correctly from Pascal to VBA, I came up with
this macro...

Function TaxWeekNumber(D As Date) As Long
Dim DD
Dim FY
FY = Year(D)
If 32 * Month(D) + Day(D) < 134 Then FY = FY - 1
DD = DateSerial(Year(D), Month(D) - 1, Day(D)) - _
DateSerial(FY, 3, 6)
TaxWeekNumber = Int(DD / 7) + 1
End Function

It worked for the date in the example (and another one I found on a
different web site); but, not being British, I have no experience with its
Tax Week Numbers ***so*** you will need to test the macro against known past
dates to make sure it really works.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Can anyone post a reliable formula for calculating week numbers in Excel

Using the code at this site

http://www.merlyn.demon.co.uk/weekcalc.htm#UKTW

and, hopefully translating it correctly from Pascal to VBA, I came up with
this macro...

Function TaxWeekNumber(D As Date) As Long
Dim DD
Dim FY
FY = Year(D)
If 32 * Month(D) + Day(D) < 134 Then FY = FY - 1
DD = DateSerial(Year(D), Month(D) - 1, Day(D)) - _
DateSerial(FY, 3, 6)
TaxWeekNumber = Int(DD / 7) + 1
End Function

It worked for the date in the example (and another one I found on a
different web site); but, not being British, I have no experience with its
Tax Week Numbers ***so*** you will need to test the macro against known
past dates to make sure it really works.

If you want it for any reason, here is a macro function to calculate the day
within the WeekNumber...

Function TaxDayOfWeekNumber(D As Date) As Long
Dim DD
Dim FY
FY = Year(D)
If 32 * Month(D) + Day(D) < 134 Then FY = FY - 1
DD = (DateSerial(Year(D), Month(D) - 1, Day(D)) - DateSerial(FY, 3, 6))
TaxDayOfWeekNumber = CStr(DD Mod 7 + 1)
End Function

If you pass April 6 of any year into it, it returns 1... so it looks like
(again, if my Pascal to VBA translation is correct) that the first day of
the week for calculating week numbers is the day of the week when April 6th
of that Tax Year (April 6th of one year to April 5th of the following year)
occurred.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Function TaxDayOfWeekNumber(D As Date) As Long

Dim DD
Dim FY
FY = Year(D)
If 32 * Month(D) + Day(D) < 134 Then FY = FY - 1
DD = (DateSerial(Year(D), Month(D) - 1, Day(D)) - DateSerial(FY, 3, 6))
TaxDayOfWeekNumber = CStr(DD Mod 7 + 1)
End Function

Whoops! Some test code left in by mistake. Since the function is returning a
Long, you don't need the CStr function call in the last line....

Function TaxDayOfWeekNumber(D As Date) As Long
Dim DD
Dim FY
FY = Year(D)
If 32 * Month(D) + Day(D) < 134 Then FY = FY - 1
DD = (DateSerial(Year(D), Month(D) - 1, Day(D)) - DateSerial(FY, 3, 6))
TaxDayOfWeekNumber = (DD Mod 7) + 1
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

Can anyone post a reliable formula for calculating week numbers

in Excel that relate to the UK Tax year?

You can read my discussions in my other posts, but since you specifically
asked for a "formula", I presume you want a worksheet formula. So, here is
my TaxWeekNumber macro function translated into a worksheet formula.

=INT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))-DATE(YEAR(A1) + (32 * MONTH(A1) +
DAY(A1) < 134), 3, 6))/7)+1

Same cautions exist as mentioned in my other postings, so make sure you give
it a good testing out before putting it to use.

Rick

 

[mlv]

You can read my discussions in my other posts, but since you specifically
asked for a "formula", I presume you want a worksheet formula. So, here is
my TaxWeekNumber macro function translated into a worksheet formula.

=INT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))-DATE(YEAR(A1) + (32 * MONTH(A1) +
DAY(A1) < 134), 3, 6))/7)+1

Same cautions exist as mentioned in my other postings, so make sure you
give it a good testing out before putting it to use.

Rick

Thanks for all your help Rick - I'll give the formulas a thorough testing.

 

[Roger Govier]

Hi Rick

With your formula, you get negative week numbers once you get past
December of the year, gradually reducing from -65 to -52.

I think the formula is nothing more complicated than
=INT((A1-DATE(2007,4,6))/7)+1
This will give week 52 for 29 March 2008, and week 53 for 04 April 2008
This is (I believe) correct but the OP must confirm with Inland Revenue,
as we do have a 53 week year from time to time.
It all depends upon which day of the week the Payroll is calculated
Most years are 52 week years, but periodically there will be a 53 week
year and the tax tables or calculations in the UK are based upon this
fact.

 

[mlv]

With your formula, you get negative week numbers once you get past
December of the year, gradually reducing from -65 to -52.

I think the formula is nothing more complicated than
=INT((A1-DATE(2007,4,6))/7)+1

For today's date (7 June 2007) both of the above formulas give the answer 9.

However, I calculate that this week is at least UK Tax Year Week No. 10, or
possibly 11.

 

[Roger Govier]

Hi

How do you calculate that?
I just loaded Sage Payroll, and for 07 Jun 2007 it gives Tax week 9. As
Sage is probably the widest used payroll package in the UK, I don't
think they will have got it wrong<g>
Tax week 10 commences on 08 Jun 2007

 

[Rick Rothstein \(MVP - VB\)]

For today's date (7 June 2007) both of the above formulas give the answer

9.

However, I calculate that this week is at least UK Tax Year Week No. 10,
or possibly 11.

According to this website (a direct link to the one I used to develop my
formulas)

http://www.merlyn.demon.co.uk/weekcalc.htm#UKTW

today is Tax Week 9, Day 7... are you say that is wrong? Here is a link
showing the definition for the UK Income Tax Weeks (which appear to be the
same as what you asked for)...

http://www.hmrc.gov.uk/manuals/nimmanual/NIM08002.htm

Using that definition, I constructed this formula

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

which appears to work for all dates now.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I think the formula is nothing more complicated than

=INT((A1-DATE(2007,4,6))/7)+1
This will give week 52 for 29 March 2008, and week 53 for 04 April 2008

I looked at the definition for Income Tax Week here


and decided your approach is correct, but I think it will make a mistake in
Leap Years because, for dates before April 6th, you are using the wrong
year. Ignoring the fact that you hard coded the 2007 (I assume you meant to
use Year(A1) instead in order to generalize the formula), that year would be
the wrong one to use for dates prior to April 6... you would need to use the
year prior to the current year in this case. Here is the formula I came up
with to account for this...

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


Rick

 

[Rick Rothstein \(MVP - VB\)]

This will give week 52 for 29 March 2008, and week 53 for 04 April 2008

I looked at the definition for Income Tax Week here


and decided your approach is correct, but I think it will make a mistake
in Leap Years because, for dates before April 6th, you are using the wrong
year. Ignoring the fact that you hard coded the 2007 (I assume you meant
to use Year(A1) instead in order to generalize the formula), that year
would be the wrong one to use for dates prior to April 6... you would need
to use the year prior to the current year in this case. Here is the
formula I came up with to account for this...

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

Hmm! I left out the link I wanted to reference. Here it is...

http://www.hmrc.gov.uk/manuals/nimmanual/NIM08002.htm

Okay, I just looked at your posting and think, perhaps, you were not going
after a generalized solution after all; rather, you gave specific
date/result examples for the current Tax Year. Given that, your approach
(hard coding the current beginning of the Tax Year) won't produce the error
I thought it might (sorry for my confusion on that)... the Leap Year problem
only becomes an issue for a generalized solution not pegged to any specific
year. With that said, I think the generalized solution formula I posted
should be correct for any date placed in cell A1 (whether past, present or
future).

Rick

 

[Roger Govier]

Hi Rick

I agree that your formula now works correctly.
However, mine will give the correct result in a leap year.
Feb 29th is always going to occur within the tax year, and my start
point is the beginning of the tax year.
I am merely taking the number of elapsed days from the start of tax year
and dividing by 7.
In a leap year, there will be more days that "qualify" for week 53 -
that is all.

No, I didn't make a mistake I meant to hard code the start of tax year,
since one is nearly always dealing with just one year.
If the OP wants a general formula, then I would either use a cell
holding the start of the tax year e.g. D1=06 Apr 2007
then the formula would be even shorter with

=INT((A1-$D$1)/7)+1
or preferably use Insert Name>Define> Name Taxyear Refers to 06 Apr
2007
and then
=INT((A1-Taxyear)/7)+1

 

[Rick Rothstein \(MVP - VB\)]

I agree that your formula now works correctly.

However, mine will give the correct result in a leap year.
Feb 29th is always going to occur within the tax year, and my start point
is the beginning of the tax year.
I am merely taking the number of elapsed days from the start of tax year
and dividing by 7.
In a leap year, there will be more days that "qualify" for week 53 - that
is all.

No, I didn't make a mistake I meant to hard code the start of tax year,
since one is nearly always dealing with just one year.

Yes, I see and acknowledged that in my other post in this sub-thread. Sorry
for my initial misunderstanding of your original post.

Rick

 

[Roger Govier]

No problems, Rick.
I didn't see your subsequent posting till after I had posted mine

 

[Rick Rothstein \(MVP - VB\)]

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

Here is a slightly modified version of the above formula that is also a
little shorter...

=1+INT((A1-DATE(YEAR(A1)-(A1 < DATE(YEAR(A1), 4, 6)),4,6))/7)

Rick

 

[mlv]

How do you calculate that?
I just loaded Sage Payroll, and for 07 Jun 2007 it gives Tax week 9. As
Sage is probably the widest used payroll package in the UK, I don't think
they will have got it wrong<g>
Tax week 10 commences on 08 Jun 2007

I accept I may have lost the week number plot

Some more information:

This week number routine is required for an Excel invoice sheet that I use.
I always use ISO week numbers on my invoices (this week is ISO week 23).

My main client says they use Tax Year week numbers, but it seems that their
system assumes that the week with 1 April in it (ISO week number 13) is Tax
Year week 1. This week (ISO week number 23) would then become my client's
Tax Year week number 11.

If we accept that the week with 6 April 2007 in it (2 - 8 April, ISO week
number 14) is the first week of the new Tax Year, then this week (ISO week
number 23) must be Tax Year week number 10.

However, (according to Sage) it seems that the week with 6 April 2007 in it
is considered to be the last week of the old 2006-07 Tax Year, and ISO week
number 15 (9 - 15 April) is the first week of the new 2007-08 tax year.
This week (ISO week number 23) then becomes Tax Year week number 9.

I suppose it doesn't help that ISO week numbers run from Monday to Sunday,
whilst Tax Year week numbers appear to run from Friday to Thursday (if Tax
week 10 commences on Friday, 08 Jun 2007, as stated above).

 

[Ron Rosenfeld]

Hi

Can anyone post a reliable formula for calculating week numbers in Excel
that relate to the UK Tax year?

I guess week 1 would be the week with 6th April in it, or maybe week 1 is
the first full week after 5th April. Does anyone know?

Thanks

=INT((A1-DATE(YEAR(A1)-(A1<DATE(YEAR(A1),4,6)),3,30))/7)
--ron

 

[Roger Govier]

Hi Mike

Although I said I used Sage for a quick check up (saved me hunting
around the IR website), Sage operate exactly as per the IR Rules, which
is the same as Rick and I did when compiling our formulae.

ISO tax weeks have week 1 as the first week which contains a
Thursday.This year, Ist Jan happened to be a Monday, hence the start of
ISO week 1 was 01 Jan 2007. Had 1st Jan occurred on a Friday, then week
1 would have started on 04 Jan 2007.

You cannot therefore use a constant offset for Tax week number compared
with ISO week number.
The Tax week numbering always starts from 6th April in the every year,
regardless of which day of the week that happens to fall upon. For 2007,
it happens to fall on a Friday.