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)

 

Tags:

One Response to “Calculated Columns – Week of month”

  1. Savitha July 6, 2015 at 8:37 pm #

    Thanks for the formula. It worked for me

Leave a Reply

%d bloggers like this: