0

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.

1 Answer 1

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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.