Week Numbers for Tax Year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mlv - 06 Jun 2007 19:55 GMT

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

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Bob I - 06 Jun 2007 20:36 GMT

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

> Hi
>
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

Rick Rothstein (MVP - VB) - 06 Jun 2007 21:22 GMT

> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 06 Jun 2007 21:37 GMT

>> Can anyone post a reliable formula for calculating week numbers in Excel
>> that relate to the UK Tax year?
[quoted text clipped - 23 lines]
> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 06 Jun 2007 21:45 GMT

> Function TaxDayOfWeekNumber(D As Date) As Long
> Dim DD
[quoted text clipped - 4 lines]
> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 06 Jun 2007 22:36 GMT

> 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

Reply to this Message

mlv - 07 Jun 2007 09:55 GMT

Rick Rothstein wrote:

> 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
[quoted text clipped - 7 lines]
>
> Rick

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

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Roger Govier - 07 Jun 2007 12:08 GMT

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.

Signature

Regards

Roger Govier

>> Can anyone post a reliable formula for calculating week numbers
>> in Excel that relate to the UK Tax year?
[quoted text clipped - 11 lines]
>
> Rick

Reply to this Message

mlv - 07 Jun 2007 14:02 GMT

> 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

>> Rick Rothstein wrote:
>>
>> =INT((DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))-DATE(YEAR(A1) + (32 * MONTH(A1)
>> + DAY(A1) < 134), 3, 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.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Roger Govier - 07 Jun 2007 14:52 GMT

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

Signature

Regards

Roger Govier

>> With your formula, you get negative week numbers once you get past
>> December of the year, gradually reducing from -65 to -52.
[quoted text clipped - 12 lines]
> However, I calculate that this week is at least UK Tax Year Week No.
> 10, or possibly 11.

Reply to this Message

mlv - 08 Jun 2007 10:25 GMT

> 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).

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Roger Govier - 08 Jun 2007 13:24 GMT

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.

Signature

Regards

Roger Govier

>> How do you calculate that?
>> I just loaded Sage Payroll, and for 07 Jun 2007 it gives Tax week 9.
[quoted text clipped - 29 lines]
> Thursday (if Tax week 10 commences on Friday, 08 Jun 2007, as stated
> above).

Reply to this Message

Roger Govier - 08 Jun 2007 17:55 GMT

I meant to add, you should ask your client which tax week he is actually
using.
If he is counting a week commencing 01 Apr 2007 as week 1, then he is
wrong. No question about it and an IR inspection could prove costly.

You also said that the week with 06 Apr 2007 in it is considered to be
the last week of the old tax year.
Depending upon which day of the week your client makes his payments. It
could have been week 53 of the previous year for him for this year, but
it will not be next year.

The start point is fixed as I have said in earlier emails and it is 06
April each year. It never varies. It doesn't matter what day that falls
upon.

Signature

Regards

Roger Govier

> Hi Mike
>
[quoted text clipped - 46 lines]
>> Thursday (if Tax week 10 commences on Friday, 08 Jun 2007, as stated
>> above).

Reply to this Message

Rick Rothstein (MVP - VB) - 08 Jun 2007 15:39 GMT

See inline comments...

> 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).

Once we figure out what to give you as a Tax Week Number (see other comments
as there are still questions remaining), are you also going to need a
translation of that Tax Week Number to the ISO Week Number?

> 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.

As Roger points out in his response to this same message I am responding to,
ISO Week Number 1 does not have to include January 1st in it depending on
whether the week with January 1st contains a Thursday or not, so I am not so
sure that April 1st will always land in ISO Week Number 13. And the
"Thursday Rule" for ISO week numbering would make a constant relation to
your client's Tax Week Number calculation out of the question as, if your
statement is correct, the day April 1st falls on is unimportant in
determining your client's Tax Week Number... Tax Week Number 1 is whatever
week contains April 1st. Of course, using this definition completely ignores
the IR April 6th rule for the start of the Tax Year's weekly numbering.

> 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
[quoted text clipped - 4 lines]
> 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 think, when looking for the last week of the Tax Year, you need to look at
April 5th, not April 6th as they may not both occur within the same weekly
time period.

> 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).

The "real" Tax Week Number day-to-day-runs change yearly... the "real" Tax
Week Number 1 always starts on April 6th (no matter what day of the week
that is) and ends on April 12th, the "real" Tax Week Number 2 always starts
on April 13th and ends on April 19th, etc.

Alright, so I am confused now.<g> Can you spell out what criteria you want
to govern when Tax Week 1 occurs (in general, not for just this year)? It
looks like you (well, I should say you client) wants it to be the full
calendar week that contains April 1st (not April 6th)... is that correct?
What does your client consider a calendar week... Sunday to Saturday or
Monday to Sunday? Does the ISO Thursday Rule factor into the week numbering
when using the April 1st Rule that your client appears to use?

Rick

Reply to this Message

mlv - 11 Jun 2007 11:38 GMT

Rick Rothstein wrote:

> Alright, so I am confused now.<g> Can you spell out what criteria you want
> to govern when Tax Week 1 occurs (in general, not for just this year)? It
[quoted text clipped - 3 lines]
> Monday to Sunday? Does the ISO Thursday Rule factor into the week
> numbering when using the April 1st Rule that your client appears to use?

You're confused? Me too! :-)

Today I spoke with my client who insisted they use Tax Year week numbers to
the IR rules.

They then came back to me to say they got it wrong this year and had week 1
(2007-08), where they should have had week 53 (2006-07). They assure me
they will get it right next year!

I shall simply use the IR Tax Year week formula with a +1 correction for
this year and next year the unaltered formula should be fine.

Thanks to you and everyone else for the useful input.

Signature

Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

Reply to this Message

Rick Rothstein (MVP - VB) - 07 Jun 2007 15:45 GMT

> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 07 Jun 2007 15:52 GMT

> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 07 Jun 2007 16:29 GMT

>> This will give week 52 for 29 March 2008, and week 53 for 04 April 2008
>
[quoted text clipped - 9 lines]
>
> =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

Reply to this Message

Roger Govier - 07 Jun 2007 16:50 GMT

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

Signature

Regards

Roger Govier

>> I think the formula is nothing more complicated than
>> =INT((A1-DATE(2007,4,6))/7)+1
[quoted text clipped - 14 lines]
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 07 Jun 2007 17:07 GMT

> I agree that your formula now works correctly.
> However, mine will give the correct result in a leap year.
[quoted text clipped - 7 lines]
> 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

Reply to this Message

Roger Govier - 07 Jun 2007 17:25 GMT

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

Signature

Regards

Roger Govier

>> I agree that your formula now works correctly.
>> However, mine will give the correct result in a leap year.
[quoted text clipped - 12 lines]
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 07 Jun 2007 19:52 GMT

> =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

Reply to this Message

Ron Rosenfeld - 08 Jun 2007 12:13 GMT

>Hi
>
[quoted text clipped - 5 lines]
>
>Thanks

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

Reply to this Message