Calculated Columns – Week of month
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)
Thanks for the formula. It worked for me