leave/sick taken problem

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,409
Messages
5,595,973
Members
414,035
Latest member
billbumkins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top