calculate week numbers UK tax year

[Abhijeet]

Hi
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?

I have UDF formula but i want as per UK Tax year please help me

 

[Luke M]

According to Google, the UK tax year starts on Apr 6. This is 95 days after Jan 1. Thus, to calculate weeknum based on UK tax year is:
=WEEKNUM(YourDate-95,Arg2)

Arg2 should be some integer saying which day you want to count as first day of the week. The function wizard will let you know what the choices are.

 

[Abhijeet]

Thanks Luke M

This work but tell me how to change in macro that UDF how this work

 

[Luke M]

Having no idea where you got that macro, or what it's purpose is, I'm going to take a guess that you still need to subtract 95 days. But that's just a guess. Why try to build a complicated UDF when the regular function is sufficient?

 

[Abhijeet]

Luke M
I have this macro this macro pull all Fridays date i want highlight in conditional formatting as per current week(UK Tax year) please tell me u have any solution for this

Sub PullFridays2()
    Dim dStart As Date
    Dim dEnd As Date
    Dim rw As Integer

    dStart = Range("A2").Value
    dEnd = Range("A3").Value

    rw = 2
    While dStart < dEnd
        If Weekday(dStart) = vbFriday Then
            Cells(rw, 3).Value = dStart
            Cells(rw, 3).NumberFormat = "m/d/yyyy"
            rw = rw + 1
        End If
        dStart = dStart + 1
    Wend
End Sub

 

[Luke M]

I'd skip the macro and just apply a regular conditional format. Again, I've no idea what this macro's background is, or how it applies to the tax year in UK. The CF formula to highlight Fridays is
=WEEKDAY(A2,1)=6

 

[Abhijeet]

I have all Fridays dates from that i want to highlight current week date i tried but i am not sure how its correct please tell me it is correct or not

 

[Luke M]

If we're just testing if the date is same week as today, then it doesn't matter whether we use standard or UK week numbering. CF Formula can then be:
=WEEKNUM(TODAY(),1)=WEEKNUM(C2,1)

 

[Abhijeet]

Thanks Luke M