Well like most people, they found a way to ask for more. So far the spreadsheet works as intented. the new problem is that frequently, many folks, who have a medical appointment, will take a whole day off and change the med apt time to sick leave and the balance of the day to annual leave.

example: 2 hour appointment out of an entire day taken off = s2/a6.

so the question now is how do we get excel to use the current formula from the previous thread, and yet be able to pick up instances in which the day off is split up?

here is the formula we have so far:

LEAVE COLUMN

{=SUM(IF(LEFT(C4:P4,1)="A",LOOKUP(C4:P4,{"A.25","A.50","A.75","A1","A1.25","A1.50","A1.75","A2","A2.25","A2.50","A2.75","A3","A3.25","A3.50","A3.75","A4","A4.25","A4.50","A4.75","A5","A5.25","A5.50","A5.75","A6","A6.25","A6.50","A6.75","A7","A7.25","A7.50","A7.75","A8","A8.25","A8.50","A8.75","A9"},{0.25,0.5,0.75,1,1.25,1.5,1.75,2,2.25,2.5,2.75,3,3.25,3.5,3.75,4,4.25,4.5,4.75,5,5.25,5.5,5.75,6,6.25,6.5,6.75,7,7.25,7.5,7.75,8,8.25,8.5,8.75,9})))}

SICK COLUMN

{=SUM(IF(LEFT(C4:P4,1)="S",LOOKUP(C4:P4,{"S.25","S.50","S.75","S1","S1.25","S1.50","S1.75","S2","S2.25","S2.50","S2.75","S3","S3.25","S3.50","S3.75","S4","S4.25","S4.50","S4.75","S5","S5.25","S5.50","S5.75","S6","S6.25","S6.50","S6.75","S7","S7.25","S7.50","S7.75","S8","S8.25","S8.50","S8.75","S9"},{0.25,0.5,0.75,1,1.25,1.5,1.75,2,2.25,2.5,2.75,3,3.25,3.5,3.75,4,4.25,4.5,4.75,5,5.25,5.5,5.75,6,6.25,6.5,6.75,7,7.25,7.5,7.75,8,8.25,8.5,8.75,9})))}

THANKS IN ADVANCE