+ Reply to Thread
Results 1 to 13 of 13

vacation accrual based on completion of 90 day probation

  1. #1
    Registered User
    Join Date
    11-29-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    vacation accrual based on completion of 90 day probation

    Hi,
    I need to create a tracking sheet for my vacation. The vacation starts to accrue here after a 90 day probation following the hire date. Each individual is allowed 40 hrs/year. I also need to see the hours accrued, the hours used & the balance. Everyone's balance renews on their anniversary. I will fill in the blanks for the names in one column & the hire date in the next column and I would like formulas for the following columns. Please help. Thank you.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: vacation accrual based on completion of 90 day probation

    Welcome to the Forum Ashstewart!

    I suggest you start by attaching an Excel file that shows how you want to set up this up, and indicate which columns you need formulas for. Then we can just fill it in, rather than creating the file for you.

    To post a file:
    1. Under the text box where you type your reply click the Go Advanced button.
    2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
    3. In the pop-up, click Add then Select to select a file from your local drive
    4. Click the Upload button to upload the file
    5. Click Done to attach it.

    It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-29-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: vacation accrual based on completion of 90 day probation

    I know it appears simple, but I just can't seem to wrap my head around it right now. I previously have worked for large corporations and now I'm working for a small company. I'm actually working backwards. They have been in business for 4 years now & still haven't put anything in place for 45-50 employees vacation calculations.
    In Column C needs to be the last day of the 90 day probation and the start of the accrual.
    In Column D needs to be how many hours accrued to date.
    In Column E needs to be how many hours used. This I will populate from our database.
    In Column F needs to be the balance of D & E.
    I hope this helps. Thank you for your time & help.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    11-21-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2013
    Posts
    159

    Re: vacation accrual based on completion of 90 day probation

    Hi,

    Copy & paste in C3 = IF(B3="","",B3+90)
    Copy & paste in D3 = IF(B3="","",(TODAY()-B3)*24&" hours")
    Copy & paste in F3 = IF(E3="","",SUBSTITUTE(D3," hours","")-E3&" Hours")

  5. #5
    Forum Contributor
    Join Date
    11-21-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2013
    Posts
    159

    Re: vacation accrual based on completion of 90 day probation

    Ashstewart,

    If it is coming like this >>> 41392 you need to change the format to date formate.

    to change the format = CTRL + 1, go to date or select custom to personalized the view.

    Regards,

    Reimar_rem

  6. #6
    Registered User
    Join Date
    11-29-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: vacation accrual based on completion of 90 day probation

    Thank you.
    However, column D (Cell D3) - the total accrued should not ever exceed 40hrs. They only get 40 hours per year.
    Can you help me fix that, if so, everything will be good!

  7. #7
    Forum Contributor
    Join Date
    11-21-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2013
    Posts
    159

    Re: vacation accrual based on completion of 90 day probation

    Try this in D3
    =IF(B3="","",IF(((TODAY()-B3)*24)>40,40&" hours",((TODAY()-B3)*24)&" hours"))


  8. #8
    Registered User
    Join Date
    11-29-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: vacation accrual based on completion of 90 day probation

    ok, Sorry, but I think we still have an issue.
    I need the total accrued to show it counting up to their 40hrs. starting from the anniversary date (I forgot about this column.) Their accrual count starts over every year on their anniversary. They don't get all 40hrs at once. They have to accrue them and that's what I need to see is the actual count towards the 40 hours per year per person to date. For example if an individual was hired 01/28/13 they wouldn't start to accrue vacation time until 04/28/13. So if they called me tomorrow to find out how many hours vacation they have, I need to be able to tell them what they have accrued so far. Then on 04/28/13 it will start the count all over again and for every year after that.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    11-21-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2013
    Posts
    159

    Re: vacation accrual based on completion of 90 day probation

    HI

    Try this:

    =IF(IF(40<(TODAY()-D3)*(40/(DATE(YEAR(D3)+1,MONTH(D3),DAY(D3))-D3)),40,(TODAY()-D3)*(40/(DATE(YEAR(D3)+1,MONTH(D3),DAY(D3))-D3)))<0,0,IF(40<(TODAY()-D3)*(40/(DATE(YEAR(D3)+1,MONTH(D3),DAY(D3))-D3)),40,(TODAY()-D3)*(40/(DATE(YEAR(D3)+1,MONTH(D3),DAY(D3))-D3))))


  10. #10
    Forum Contributor
    Join Date
    11-21-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2013
    Posts
    159

    Re: vacation accrual based on completion of 90 day probation

    Hi,

    Please find attachment vacation.xlsx



    Regards,

    reimar_rem

  11. #11
    Registered User
    Join Date
    11-29-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: vacation accrual based on completion of 90 day probation

    Looks good! Thank you!
    The "Reset Column" - Do I have to manually change the year every year for it to reset or will it automatically do that?

    Thanks again!

  12. #12
    Forum Contributor
    Join Date
    11-21-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2013
    Posts
    159

    Re: vacation accrual based on completion of 90 day probation

    Hi,

    If you are referring to my attachment. Reset column is manually encoded.


  13. #13
    Forum Contributor
    Join Date
    11-21-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2013
    Posts
    159

    Re: vacation accrual based on completion of 90 day probation

    Hi

    Pleas find automated reset column for after laspse on 1 year.

    vacation automated reset colomn.xlsx



    If this post helps you, please click * star left side below my post to add reputation.

    Thanks,

    Reimar

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 10-16-2013, 09:05 PM
  2. Vacation Accrual Help Please
    By meric327 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-08-2013, 04:21 PM
  3. Formula for Vacation Accrual Days Based on Anniversary Date
    By Mustang03 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-23-2010, 09:34 PM
  4. vacation accrual formula based on previous year avg hours worked
    By lfox in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2009, 03:41 PM
  5. Vacation accrual based on hours worked - Excel 2003
    By CCB AA in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-20-2005, 04:40 PM

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