How I can set up a relationship with a date in Microsoft Excel?
I'm trying to set up a tracking sheet and wondered assistance if this is possible: The holidays are directly linked to time of employment thus establishing the holidays: Year 1 = 15 days 12 months = 1.25 ratio second year = 16 days over 12 months = 1.33 regarding third year = 17 days over 12 months = 1.4166 year relationship quarter = 18 days over 12 months = 1.50 year relationship fifth = 19 days over 12 months = 1.5833 relationship sixth year = 20 days over 12 months 1,66 Di employee relationship begins April 1, 2008 – the ratio would be 1.25 for the first year since April 2009 the ratio would be 1.33 Say First in October that a column I'd like to show their holidays in automatically 7.50 December 1 would be 10 days of accrued vacation On 15 December that would be about 10.625 days accumulated like the accrued vacation column that is updated automatically. Any help is greatly appreciated. Thanks
The solution is to use user-defined function, because no native function excel or simple formula can. Alt + F11, right-click an icon on the left side, insert module, double click module to add the following code. ACCVAC function (startRange, endRange) If IsDate (startRange) = False Or IsDate (endRange) = False Then ACCVAC = "# DATE!": Exit Function endRange ed =: sd = plen = DateSerial startRange (Year (ed), month (ed), Day (ed)) – DateSerial (year (sd), month (sd), Day (sd)) + 1 If> Plen = 365 Then yrNumAcc = Int (plen / 365) * 15 + Int (plen / 365) / 2 * (Int (plen / 365) – 1): adj = 1 resDay = (plen Mod 365) / * 365 (WorksheetFunction.RoundUp (plen / 365, 0) + 14 + adj) = End ACCVAC resDay yrNumAcc + Function After completing this, suppose your start date in A1 1Apr08, 1Oct08 end date in B1, the formula ACCVAC to use is = (A1, B1) and then return 7.561643836 if A1 and B1 is 1Apr08 is 31May09, formula returns 15 if A1 and B1 is 1Apr08 30Oct10, formula returns 41.50410959 Note: the role of assumptions, if the start date and the date is 1Apr08 2Apr08 final, day count is 2 and not 1. If you think it should be a draw then the + 1 in this line of code above plen = DateSerial (year (ed), month (ed), Day (ed)) – DateSerial (year (sd), month (sd), Day (SD)) + Please e-mail for more information
AeA Employment Law Update for 2009
