<< What have I been doing? | SharePoint Designer Workflow – Send Email Reminder X days prior to a Date >>

SharePoint List Calculated Column - Calculate Business Days

by Oscar 24. June 2008 03:19

Ran into a nice little scenario where my client wanted to know how many business days were remaining prior to a Task Due Date. Once I had this date, I built a nice DataViewWebPart (see Figure 3) with the SharePoint Designer! This is what I came up with, hope it helps you!

A) Create a calculated column; call it something like "Days Prior to Due Date" in my case.

b) Paste this value into a Calculated Column, formula
=(DATEDIF(Today,[Due Date],"d"))-INT(DATEDIF(Today,[Due Date],"d")/7)*2-IF((WEEKDAY([Due Date])-WEEKDAY(Today))<0,2,0)+1

C) Make the output type "Single line of text"

NOTE: At the time of creating this calculated column, you must have another dummy column called "Today", it does not matter what data type it is. Once you create your calculated column, make sure to delete the Today column or your values will not work!

FIGURE 1 – Shows the formula for your calculated column to count only weekdays.

FIGURE 2 – Shows the sample Due Date and the calculated column output (far right)

FIGURE 3 – Shows you a dashboard with some XSL logic in the background to output an image on the left, and the days remaining calculated column value on the far right

Tags:

SharePoint Calculated Column – Calculate Business Days Only, exclude weekends! A while back, I talked about only taking into account business days on any SharePoint Designer wor...Creating an Org Chart using Visio 2007 and linking shapes to a SharePoint ListMany times during a project, you hit a requirement to have the ability to display an org chart on yo...SharePoint Designer Workflow – Send Email Reminder X days prior to a Date Business Problem One of my clients wanted to send email reminders to users one week prior to ...

Comments

6/25/2008 5:12:22 AM #

Trackback from Oscar Medina's Blog

SharePoint Designer Workflow

Oscar Medina's Blog | Reply

7/15/2008 2:06:04 PM #

When using today in a formula, I think it shows the first date of the computer (January 1st, 1899 or so). =weekday([Today)) shows an incorrect day and =DATEDIF([DATEOFTHEEVENT];[today];"d") gives number higher than 39000.

¿How can i use today in formulas correctly?

Thanks in advance.

Javier | Reply

7/21/2008 6:05:45 AM #

Javier, thanks for stopping by. Make sure you

a) create a Today (any data type) column, before creating your calculated column.
b) delete the Today after your calculated column is saved!

Oscar | Reply

8/13/2008 2:57:50 AM #

Javier, make sure you delete the 'Today' dummy column in order to see the correct calculated date.

Oscar | Reply

9/5/2008 6:12:00 AM #

Hi,

Loving your blog = ) I was wondering how did you show the images on the left. I know it is done by using XSL, but how?

Lauri V | Reply

9/5/2008 5:23:50 PM #

Hi Lauri,

Thanks for stopping by. In XSL you can build an image src using this technique:

<img alt="{@Title}" border="0">
  <xsl:attribute name="src">
      
      <xsl:if test="$TotalTasks != $CompletedCount">
        <xsl:choose>
        
          <xsl:when test="$AmberCount > 0 and $NotStartedAndPastDueCount=0 and $RedCount=0">/_layouts/images/ewr214l.gif</xsl:when>
          
          <xsl:when test="$NotStartedAndPastDueCount > 0">/_layouts/images/ewr213l.gif</xsl:when>
          
          <xsl:when test="$NotStartedAndPastDueCount > 0 or $RedCount > 0 or $AmberCount > 0">/_layouts/images/ewr213l.gif</xsl:when>
        </xsl:choose>
      </xsl:if>

      
      <xsl:if test="$TotalTasks = $CompletedCount">
        /_layouts/images/ewr212l.gif
      </xsl:if>
      
      <xsl:if test="$AmberCount = 0 and $RedCount=0 and $CompletedCount=0">
        /_layouts/images/ewr226l.gif
      </xsl:if>

      <xsl:if test="$CompletedCount > 0 and $AmberCount=0 and $RedCount=0">
        /_layouts/images/ewr212l.gif
      </xsl:if>

    </xsl:attribute>
    </img>

Oscar | Reply

10/7/2008 4:04:23 PM #

Hey Oscar,

Any thoughts on auto recalculating or auto updating the calculated columns. I have to manually update the list item in order to update the value in calculated column.

Regards,
Ritesh

Ritesh | Reply

3/25/2009 1:42:32 PM #

I was just thinking about Calculate Business Days and you’ve really helped out. Thanks!

Thomas Richards | Reply

4/18/2009 1:41:02 AM #

Ravi | Reply

4/18/2009 1:54:05 AM #

Oscar,
It’s really nice article to understand the calculated field,

could you please tell me how to calculate working days between two dates.
Actually I applied same formula for finding working days between two dates, its working fine, but for some dates it’s not showing the result properly ,some dates particularly
1) start date(4/15/2009) - end date(4/25/2009)actual result is 8,but it shows 9,
2) start date(4/08/2009) - end date(4/18/2009)actual result is 8,but it shows 9,
could you help me out to get the actual working days between two dates

I found some solution for NumberOfWorkDays but I am very new to XSLT coding , to Can u help me to convert the below code to  XSLT code

Public Function GetNumberOfWorkDays(sStartDate, sEndDate)
    Dim iDays
    Dim iWorkDays
    Dim sDay
    Dim i

    iDays = DateDiff("d", sStartDate, sEndDate)

    iWorkDays = 0

    For i = 0 To iDays
      'First day of the week is sunday
      sDay = Weekday(DateAdd("d", i, sStartDate))
      If sDay <> 1 And sDay <> 7 Then
        iWorkDays = iWorkDays + 1
      End If
    Next
    GetNumberOfWorkDays = iWorkDays
  End Function

Thanking You
Ravi

Ravi | Reply

5/30/2009 11:54:27 AM #

That’s great, I never thought about these type of formulas but its brilliant.

Terry Walker | Reply

7/3/2009 4:54:08 PM #

YOU ARE A LEGEND!!
THANKS HEAPS FOR THIS POST. I needed to calculate task duration and exclude weekends. I replaced today with [Start Date] and it works like a charm.
Other formulas I found before were all lengthy and too buggy...

Ali Sanaei | Reply

7/3/2009 6:58:55 PM #

Thanks to all for the great comments! I have been super busy with interesting projects! I will be publishing more findings on recent work I've done, stay tuned!

Oscar | Reply

7/31/2009 10:06:13 PM #

Thanks, folks! This really saved the day!

Annette | Reply

9/14/2009 2:20:53 AM #

I leave a lot of comments on a lot of blogs each week - but there is one situation where I rarely leave a comment - even if the post deserves it.Good work

payday loans | Reply

9/16/2009 6:25:06 PM #

Boa tarde,
Gostava de configurar uma coluna numa lista de sharepoint que calculasse os dias desde que começou um trabalho ate que terminasse. Pode ajudar, por favor? É que usei esta formula, mas não está funcionando

=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),DAY(Today)<DAY(Created))),1,0)&" years, "&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today)<=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)>=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&" months, "&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&" days"

iolanda | Reply

9/17/2009 5:49:00 PM #

This is the best post on this topic i have ever read.

Regards

Pitter

aion kina | Reply

9/17/2009 8:36:12 PM #

Me and my friend were arguing about an issue similar to this! Now I know that I was right. lol! Thanks for the information you post.

Pop Up Displays | Reply

9/25/2009 12:21:25 AM #

Your blog is so informative � keep up the good work!!!!

cash loans | Reply

12/29/2009 4:10:55 PM #

Pingback from levithan.com

Calculate Business Days between start and complete date at Collaboration Now

levithan.com | Reply

2/3/2010 8:06:25 PM #

Can anyone tell me how to calculate a due date when an item is created? I can do a simple =Created+3, but how do I skip weekends? I realize that i'll miss holidays, but i don't want to manage an external list every year, so i can live without the holidays being taken into consideration.

It is for an InfoPath Doc Library. Any guidance is appreciated!

Regards,
Elbio

Elbio | Reply

6/19/2010 5:09:12 AM #

Pingback from lnmykys.com

Prueba de post | Marketing Online

lnmykys.com | Reply

Add comment