פוסטים אחרונים

How to calculate Week Number by SharePoint formula

תגיות

WSS MOSS SharePoint week number

ניווט

ארכיון

June 2008 (1)

How to calculate Week Number by SharePoint formula

Many times I was asked to filter Task List by week number (e.g show this week task) .

In this post, I'll Explain how to calculate week number with SharePoint formula.

1. First step, create your SharePoint list and add calculate filed.

2. Add the following formula

=INT(([Start Date]-DATE(YEAR([Start Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Start Date]),1,1)),"d")))/7)+1

Formula description

= ({Calculate the distance between the begging of the year to the current date} + {the first day of the week in which the year begins})/ {Number of day per week} + {1}

Enjoy

פורסם: Jun 26 2008, 04:55 PM by sps | with 11 comment(s)

תגים:WSS MOSS SharePoint week number

תוכן התגובה

Tyler Wallace כתב/ה:

This is an excellent post. Is there a way to filter by 3rd Wednesday of every month? Thanks!

# June 29, 2008 6:58 PM

sps כתב/ה:

Yes, You just need to find the first Wednesday of the current month and add 21 days to it.

Tnx Ziv

# July 4, 2008 6:22 PM

Pratap כתב/ה:

Very helpful, Thanks

# August 20, 2008 12:27 PM

Sean כתב/ה:

So what happens if you span years? Can you calculate age over years? Looks like the year boundary is a problem. I thought I would see if you had an easy solution before I go and try to hack one up myself.

Thanks!

Sean.

# September 22, 2008 1:36 PM

anup כתב/ה:

How to calculate total number of sunday in 1 month.

Is it 4 or 5.

But how to calculate.

# July 17, 2009 1:32 PM

Julien כתב/ה:

I found a problem.. this year 2009, the 1 of January is not on the first week, then if you calculate the 31 of December you get 53, last time I checked there aren't 23 weeks in a year. This is really close, but not close enough.

# August 19, 2009 4:44 PM

Guido כתב/ה:

how can you get the current date in stead of the [start date]. I want to have the current weeknumber each week in a workflow. The system must get the current date in de workflow en convert this to an weeknumber.

# September 10, 2009 12:36 PM

Gagneesh כתב/ה:

Why is 1 being added to the end? It appears to me that removing the 1 results in correct calculation for the week.

Julien has also pointed out that the formula results in 53 weeks. The first week of the ISO format depends on the number of days in the first week (it is this year's first week if more than 3 days of the week are in January else it is last week of the previous year).

Please review and provide updates.

# October 12, 2009 11:35 PM

Sarav כתב/ה:

Very Helpful! keep it up

Thanks A Lot

# December 28, 2009 6:56 PM

silvu כתב/ה:

For all those who live in countries where the week starts on Monday and knows that a year has 53 weeks

=If(Int(([Start Date]-Date(Year([Start Date]),1,1)+(TEXT(Weekday(Date(Year([Start Date]),1,1)-2),"d")))/7)=0,53,Int(([Start Date]-Date(Year([Start Date]),1,1)+(TEXT(Weekday(Date(Year([Start Date]),1,1)-2),"d")))/7))

# November 29, 2010 7:43 AM