how much sick time used

**kharwell812** · 12-19-2008, 10:37 PM

i am trying to find the answer for a value that my change from time to time. the example is dealing with time, a 24 hour period. the cell entry my look lilke.... "SE/10". that is where se = sick time used out of the 24 hour period. so in the example "SE" = 14. but this may change depending on how many hours taken

**JBeaucaire** · 12-19-2008, 10:43 PM

If you can fully represent the data in a workbook and give examples of how the thing has to work including exceptions, we can help you formulate/automate it. There's no way we can help you with parameters like "SE = 14...except not always."

**kharwell812** · 12-20-2008, 08:48 PM

thanks for the help, it will take a couple of days to get the information on a post. this will be great. thanks again.

**kharwell812** · 12-20-2008, 09:55 PM

can you tell me how to post the workbook so you can see in the correct context

**protonLeah** · 12-20-2008, 10:55 PM

On the top row of the message box there is a paper clip icon:

* click it and you will get a pop up form.

* In the 'Upload files from you computer' box click the "choose" button, navigate to your (saved and closed) file double click the file, then click the "UPLOAD" button. When it is done, close the popup.

* Now, back in your message box, click the paper clip again and click your file.
You should see the attachment code inserted into your message automatically.

**daddylonglegs** · 12-21-2008, 12:04 PM

Perhaps you can just describe a little more.

Are you dealing with a range of cells containing these values, or a single cell? is "SE" the only code or are there others? Can the cells contain other types of data, e.g. just a number?

For a single cell, i.e. A1 you could get your result like this

=24-IF(LEFT(A1,2)="SE",MID(A1,4,10),0)

or to extend to a range perhaps

=SUM(IF(LEFT(A1:A10,2)="SE",24-MID(A1:A10,4,10),0))

confirmed with CTRL+SHIFT+ENTER

**kharwell812** · 12-21-2008, 12:06 PM

time card.xlsx

here is the workbook.

this is the formula for the sick column...

=IF(B8="*",AF6+10.64,AF6)-(COUNTIF(C9:AD9,"SE")*24+COUNTIF(C9:AD9,"SF")*24)

(B8="*",AF6+10.64,AF6) -- this part of the equation looks for time accrument every pay period, it works and does not figure in sick time usage.

-(COUNTIF(C9:AD9,"SE")*24+COUNTIF(C9:AD9,"SF")*24) -- this is the part dealing with sick time usage. the way it figures so far is it looks for sick usage wheather it is SE= sick employee or SF= sick family will be in the pay period. it can only figure for 24 hours.

what I am trying to do is figure partial usage, where as someone takes a partial shift. each shift is based on 24 hours. and the cell entry may llok like SE/10 where as SE would equal 14 hous used; or 8/SF where as SF would equal 16 hours used.

have i explained everything or do you need more information

**protonLeah** · 12-21-2008, 06:50 PM

I don't quite understand your calculations for sick leave. Are you calculating the time USED?

Your first formula subtracts from cell AF4 which is empty (also since it is merged with AF3 the formula should say AF3).

Do you intend that sick leave is given as a fixed amount at the beginning of the year? Should this value be in AF3?

Finally, I think it would greatly simplify your formulas if you used the cells currently used for "HD" for all leave codes. That way the formulas won't have to do a search/find function tring to match SF/SE's mixed with numbers.

sickleave-bvj.xls

**kharwell812** · 12-21-2008, 07:35 PM

yes, i am calculating time used. the cell AF3 is the starting balance from a previous time sheet. we work 24 hours on and 48 hours off. the reason for the context is so i can see what time was used in relation to the 24 hour shift. the HD is there for the required holidays... those are the dates that we earn time for the holiday bank... which is represented in the AG column. this time is also used in the same context as sick time. i believe that if i get the formula to work for sick time it will also work for HOT and VAC times as well.

time card.xlsx

i have entered an example of sick time used during the first month