Adding a number of working days to a specific date

…this post was published on 2014 July 30.

Let’s assume this

For a specific date, introduced by an user, we need to calculate a specific interval, and get the final date. This is very usefull for leaving days  apps

Let’s create a date column, COL_DAT1, and a numeric column, as hollidays

We need to calculate 3 big intervals

I) first week, full or not , and how many days are filled in it ( for example, the user could take take 3 days, Mo, Tu, We – the idea is that the holiday will not cover first week-end

II) the number of full weeks, to get the number of days in that week-ends

III) last dys in last week , if remains for that period

Step 1. First Week

The formula I propose for first week is

FIRST_WEEK_DAYS = IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))

In my testing env, WeekDAY(Monday) = 2 and so on. In your case, change the values as needed

In this first WEEK, maybe not completely filled, based on NDAYS introduced by the user, the number of taken days are

DAYS_IN_FIRST_WEEK = IF(NDAYS>FIRST_WEEK_DAYS;FIRST_WEEK_DAYS;NDAYS)

Step 2. Calculating full weeks

FULLWEEKS = TRUNC((NDAYS-FIRST_WEEK_DAYS)/5)

The number of Sundays and Saturdays needed to be added is

TOTAL_WEEK_ENDS = IF(FULLWEEKS>0;2*FULLWEEKS+2;IF(NDAYS>DAYS_IN_FIRST_WEEK;2;0))

The final value

FINAL_DATE = COL_DAT1+NDAYS+TOTAL_WEEK_ENDS – 1

Step 3. Last week

If the calculated date will be Monday, it necessary to remove 2 days ( the last incomplete week end  )

So the complete formula is

=IF(WEEKDAY(FINAL_DATE)=1;FINAL_DATE-2;FINAL_DATE)

Without any other columns, the formula is:

 

=IF(WEEKDAY((COL_DAT1+NDAYS+(IF((TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))>0;2*(TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))+2;IF(NDAYS>(IF(NDAYS>(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));NDAYS));2;0)))-1))=1;(COL_DAT1+NDAYS+(IF((TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))>0;2*(TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))+2;IF(NDAYS>(IF(NDAYS>(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));NDAYS));2;0)))-1)-2;(COL_DAT1+NDAYS+(IF((TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))>0;2*(TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))+2;IF(NDAYS>(IF(NDAYS>(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));NDAYS));2;0)))-1))
This entry was posted in CalculatedColumn, Sharepoint. Bookmark the permalink.