• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Function of a formula for distribution

uloveonce

New Member
Hello

i have this formula, which I want to distribute to my team so that they can use it as a normal formula.
Code:
=IF(ROUNDUP(((WEEKNUM(D66))/4),0) >13,13,ROUNDUP(((WEEKNUM(D66))/4),0) ) &"X"&ABS((ROUNDUP(((WEEKNUM(D66))/4),0))-((WEEKNUM(D66))/4)-1)/25*100
Basically the formula converts week number to period. E.g. first 4 weeks are period 1, next 4 weeks are period 2 and like wise. It also gives me week from that period.

e.g. 10/2/2014 which is week 40 falls on 10 period and week number is 4 so my output is "10X4". Yes I need that X thing between to differentiate period and week.I tried but failed.

Also I cannot use it on other files. Is there anyway to use it on all the files in excel.

Please help and how can I convert this to function.
 
You can use a UDF to do this

Code:
Function MyWeekNum(Target As Double) As String

Dim Temp As String
If Application.WorksheetFunction.RoundUp(((Application.WorksheetFunction.WeekNum(Target)) / 4), 0) > 13 Then
  Temp = "13"
Else
  Temp = Application.WorksheetFunction.RoundUp((Application.WorksheetFunction.WeekNum(Target) / 4), 0)
End If

MyWeekNum = Temp + "X" & Abs((Application.WorksheetFunction.RoundUp(((Application.WorksheetFunction.WeekNum(Target)) / 4), 0)) - ((Application.WorksheetFunction.WeekNum(Target)) / 4) - 1) / 25 * 100

End Function

to use simply type =MyWeekNum(D66) or =MyWeekNum(Today())

Copy the above code into a Code Module in VBA

Once this code is included in the file, the file must be saved as either as an Excel Macro (*.xlsm) or Binary (*.xlsb) file type
 
Back
Top