Requirement

Create a calculated column called ‘Progress Report No’ based on the following format:

yyyy.MM.W(WeekOfMonth).

e.g. A report created on 4/8/2011 would be, 2011.8.W1

Solution

The formula for the calculated column is:

=TEXT(Created,”yyyy”)&”.”&MONTH(Created)&”.W”&ROUND((DATEDIF(DATE(YEAR(Created),MONTH(Created),1)-(WEEKDAY(DATE(YEAR(Created),MONTH(Created),1),1))+1,Created+(7-WEEKDAY(Created,2)),”D”)+1)/7,0)