SharePoint list calculated column

Question

I appreciate any help with this problem.

It is similar to the calculated column questions on here but slightly different. What I have is:

First day of absence > Last day of absence > Half day yes or no (filled in by user) and > Then total days to be shown in a calendar view.

Problem: When a request is put through and accepted, for example an employee is sick from 9/3/15 - 11/3/15. In the calendar this only shows as 9/3/15 - 10/3/15.

I really don't know why this is doing it looking at the calculated column and was wondering if anyone can have a quick look at the formula below:

=IF([Half Day]="No",IF(AND((WEEKDAY([Last Date of Absence],2))<(WEEKDAY([First Date of Absence],2)),((WEEKDAY([First Date of Absence],2))-(WEEKDAY([Last Date of Absence],2)))>1),(((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))-(FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2)-2),(((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))-(FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2))),IF(AND((WEEKDAY([Last Date of Absence],2))<(WEEKDAY([First Date of Absence],2)),((WEEKDAY([First Date of Absence],2))-(WEEKDAY([Last Date of Absence],2)))>1),(((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))-(FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2)-2),(((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))-(FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2)))-0.5)

Its almost like I am subtracting a day so can I simply add a day instead? But then this will add a day to an employees half day request so instead of half a day will show as 1 and a half days.

Hope I have explained it well enough and thanks in advance.

Answer 1

Your Formula is too complex for a quick look, I can't solve it for you.

First step is to take it into a texteditor and add a bunch of linebreaks ands spaces. SharePoint will ignore all those when you paste it back in your Calculated Column formula.

Then clean/slim down your Formula

At first look:

• you do not need the ,2 parameter for WEEKDAY (but that is not the error)
• The 2 calculations are exactly the same apart from a -0.5 addition, move the whole calculation out before the first IF and add a 0 or -0.5 with an IF([Half day]
• Then delete all unneeded ( )

your formula, I only added some linebreaks and spaces:

=IF([Half Day]="No"
,
IF(AND(
    (WEEKDAY([Last Date of Absence],2))<(WEEKDAY([First Date of Absence],2))
    ,
    ((WEEKDAY([First Date of Absence],2))-(WEEKDAY([Last Date of Absence],2)))>1
    )
        ,
        (((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))
            -
            (FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2)-2)
        ,
        (((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))
            -
            (FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2)))
,
IF(AND(
    (WEEKDAY([Last Date of Absence],2))<(WEEKDAY([First Date of Absence],2))
    ,
    ((WEEKDAY([First Date of Absence],2))-(WEEKDAY([Last Date of Absence],2)))>1
    )
        ,
        (((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))
            -
            (FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2)-2)
        ,
        (((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1))
            -
            (FLOOR((DATEDIF([First Date of Absence],[Last Date of Absence],"D")+1)/7,1)*2)))-0.5
)