+ Reply to Thread
Results 1 to 9 of 9

how much sick time used

  1. #1
    Registered User
    Join Date
    12-19-2008
    Location
    united states
    Posts
    10

    how much sick time used

    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
    Last edited by kharwell812; 12-27-2008 at 09:19 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    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."
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    12-19-2008
    Location
    united states
    Posts
    10

    thanks, for the help

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

  4. #4
    Registered User
    Join Date
    12-19-2008
    Location
    united states
    Posts
    10

    how to post the workbook

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

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,079
    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.
    Ben Van Johnson

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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

  7. #7
    Registered User
    Join Date
    12-19-2008
    Location
    united states
    Posts
    10

    thanks for the help

    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

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,079
    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
    Last edited by protonLeah; 12-21-2008 at 06:56 PM. Reason: forgot attachment

  9. #9
    Registered User
    Join Date
    12-19-2008
    Location
    united states
    Posts
    10

    calculating time used

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1