leave/sick taken problem

[RCBricker]

I have a spreadsheet that is a 2 week schedule of time.

If the cell is blank - the employee worked

if the cell is filled - the employee took the day off by burning leave or sick time

my problem is how can I set it up so that I can enter a total into the cell with a designation (like L for leave and S for sick, thus given an in cell value of 8L for 8 hours of leave) and have the spreadsheet count up all of the values with L and all of the values with S and display the totals in the "leave balance" and "Sick Balance" Columns?

Thanks

 

[jeffreybrown]

See if something like this works...

http://www.mrexcel.com/forum/showthread.php?t=408953

 

[RCBricker]

that was it thanks

 

[RCBricker]

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