Calculated columns in SharePoint?

Question

I have 4 columns Leave Type, PTO Available, Calculated days, Current PTO YTD. Leave Type is a drop-down box which is having different types of leave like casual leave, sick leave, marriage leave.., like this.

And also PTO Available is the column which company gives some leaves for employee in a year like casual leave in a year=18 days, Sick leave=10 days like that default holidays.

And my Calculated days is the column which he will take leaves with comparison of Start Date and Return Date, which is a calculated column.

So,now my requirement is I have to take Current PTO YTD has a calculated column with different conditions like,

If Leave Type=Casual Leave then I have to compare the field casual leave in PTO Available and I have to subtract the calculated days of that particular column and the result should be in Current PTO YTD in Current Casual Leave PTO YTD .

Likewise, if Leave Type=Sick Leave the I have to compare the field Sick leave in PTO Available, I have to subtract the calculated days of the particular column and the result should be in Current PTO YTD in Current Sick Leave PTO YTD.

And also I have to exclude Saturdays and Sundays in calculated columns?

I have tried but no result. Can anyone help me in this issue?

Answer 1

Do not try and built a complex formula in one go.

Take it step by step; make LeaveType="Casual Leave" work first..

You have already written down the pseudo code in your description,
Now... best done in Notepad.. rewrite the description into a Formula..

Once you think it might work, paste it into SharePoint... If it fails.. go back to Notepad..

Tip: copy paste each try as a new paragraph in Notepad.. you learn from reviewing previous mistakes.

If Leave Type=Casual Leave 
   then I have to compare the field casual leave 
       in PTO Available 
   and I have to 
       subtract the calculated days of that particular column 
and the result should be 
    in Current PTO YTD 
    in Current Casual Leave PTO YTD .

Notes:

• Calculated Columns can not set a value in another Column, you need a Workflow for that

  IF([Leave Type]="Casual Leave"
     ,"I have to compare the field casual leave"
     .....