+ Reply to Thread
Results 1 to 8 of 8

Vacation Accrual Formula?

  1. #1
    Registered User
    Join Date
    01-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Vacation Accrual Formula?

    We have two categories of employees, Full Time and Temporary. Full time employees earn 2.5 days of vacation every month; if a Full time employee is terminated in the first 15 days of a month (date entered in column E), they get 1.5 days of the month's accrual. If terminated after the 16th, the employee will get the full month's accrual.

    Is there a way to create a formula in column Y that returns one of three values 2.5 or 1.25 for Full Time employees or 0 for Temporary.

    Thanks in advance to anyone who can assist.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    02-29-2008
    Posts
    15
    Yes, assuming that the vacation year runs from January to December, if a termination date is entered in cell E1, the formula would be:

    =((VALUE(TEXT(E1,"mm"))-1)*2.5)+IF(VALUE(TEXT(E1,"dd"))<16,1.5,2.5)

  3. #3
    Registered User
    Join Date
    01-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vacation Accrual Formula?

    Thanks gunrgraham. Don't think that's the formula I'm looking for, the formula has to be able to calculate a value even if Cell in Column E is blank ie it still needs to return a value (2.5) for employees that haven't terminated employment.

    Suggested formula also doesn't seem to address the Temporary employees who don't accrue vacation. Not sure if its possible but the formula also has to incorporate Column H. I've tired various iterations of IF funcion but can't seem to figure out a solution that would return one of 3 results (1.25 or 2.5 for Full time employees and 0 for temporary)

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Vacation Accrual Formula?

    try this in Y7:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    drag down

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  5. #5
    Registered User
    Join Date
    01-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vacation Accrual Formula?

    Getting warmer dredwolf. Formula seems to work for everything except for situation where termination date for full time employee is after the 15th of the month when value returned should be 2.5. Also see you used 1.5 in formula, actually need it to be 1.25
    Last edited by denizenXIX; 01-27-2013 at 10:27 PM.

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Vacation Accrual Formula?

    okay, how about this ?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps


    Edit-
    This may be better though :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And slimmer yet :
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by dredwolf; 01-27-2013 at 11:34 PM.

  7. #7
    Registered User
    Join Date
    01-27-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Vacation Accrual Formula?

    Perfect dredwolf. Thank you so much. The slimmer version worked. Really appreciate the help on this one.

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Vacation Accrual Formula?

    You are welcome

+ 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