Results 1 to 6 of 6

Calculate vacation eligibility dates in a certain year

Threaded View

Only4Excel Calculate vacation... 03-20-2012, 02:56 PM
Dennis7849 Re: Calculate vacation... 03-20-2012, 04:45 PM
Only4Excel Re: Calculate vacation... 03-20-2012, 04:51 PM
daddylonglegs Re: Calculate vacation... 03-20-2012, 05:50 PM
Only4Excel Re: Calculate vacation... 03-20-2012, 06:30 PM
fizataylor Re: Calculate vacation... 09-22-2014, 10:53 PM
  1. #1
    Registered User
    Join Date
    08-21-2011
    Location
    Sri Lanka
    MS-Off Ver
    Excel 2007
    Posts
    17

    Arrow Calculate vacation eligibility dates in a certain year

    Hello Everyone,

    Our vacation plan is as follows. DOH = Date of Hire

    DOH+110, DOH+220, DOH+365, DOH+475, DOH+585, DOH+730 etc....

    means 1st vacation 110 days after DOH, 2nd after DOH+110+110, 3rd after DOH+110+110+145, 4th one after DOH+110+110+145+110 etc so basically 3 times in 365 days.

    My question how can I calculate the vacation eligibility dates in a specific year & it's position number?

    eg: if i want to know the dates in 2012 1st one is 4/19/2012, 2nd 8/7/2012, 3rd one is on 12/30/2012

    I got this to work with helper cells.

    A1 = DOH

    B2:B30 to down, 110, 110, 145, 110, 110, 145, 110, 110, 145 etc...the in A2:A30 to down =A1+B2

    E1 is the date 1/1/2012

    1st vacation in D3 =INDEX(A2:A30,MATCH(E1,A2:A30)+(LOOKUP(E1,A2:A30)<E1))
    2nd n D4: =INDEX(A$2:A$30,MATCH(D3,A$2:A$30,0)+1)
    3rd in D5 : =INDEX(A$2:A$30,MATCH(D4,A$2:A$30,0)+1)

    And it's psition number is MATCH dates in A2:A30, which 22, 23, 24 respectively.

    How can I do this wihout helper cells?

    Thank you fr your time

    Raj
    Last edited by Only4Excel; 03-20-2012 at 07:00 PM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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