 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2007Counting instances in a period
Norwegian law says that the employer have to pay for the first 16 day period when a person is on sick leave, after that period the state compensates the employee. So if a emplyee was taken ill in this pattern, the employer will pay her for all instances except the last, since it is outside the 16 day period, but the illness started within it. Do anyone have a good formula that I could use to calculate which days I have to pay for and which I don't? 01.01.07 ill have to pay 02.01.07 03.01.07 04.01.07 05.01.07 06.01.07 07.01.07 08.01.07 09.01.07 ill have to pay 10.01.07 11.01.07 12.01.07 13.01.07 14.01.07 15.01.07 ill have to pay 16.01.07 ill have to pay 17.01.07 ill state pays /pelle =SUMPRODUCT((DAY(B1:B31)>16)*(C1:C31))  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Norwegian law says that the employer have to pay for the first 16 day > period when a person is on sick leave, after that period the state [quoted text clipped - 25 lines] > > /pelle Hmmm... I can't get it to work... I am looking for the formula that returns "have to pay" or a sum I have to pay. Also If the employee falls ill after the first 16 days, the period that I have to pay starts over. The period is 16 days from the first time the employee fell ill. 01.01.07 ill have to pay 02.01.07 03.01.07 04.01.07 05.01.07 06.01.07 07.01.07 08.01.07 09.01.07 ill have to pay 10.01.07 11.01.07 12.01.07 13.01.07 14.01.07 15.01.07 ill have to pay 16.01.07 ill have to pay 17.01.07 ill state pays 18.01.07 19.01.07 ill have to pay for another 16 days... > =SUMPRODUCT((DAY(B1:B31)>16)*(C1:C31)) > [quoted text clipped - 31 lines] > > > /pelle Can you clarify the rules a bit more? Do the employer/state rules apply only to consecutive days for the same illness? Are the days cumulative within a certain period of time? Example: Illness_1....15 days (employer pays) Illness_2....a month later, 5 days...(Employer pays 1 day,State pays 4 days) Do you post illness start and end dates? Are you tracking days for ALL calendar days and flag sick days or do you only post sick days? Are all employees' sick days tracked in one list or is there a separate list for each employee? *********** Regards, Ron XL2002, WinXP > Norwegian law says that the employer have to pay for the first 16 day > period when a person is on sick leave, after that period the state [quoted text clipped - 25 lines] > > /pelle Googled Though not quite as generous, other European governments offer similar sick-pay packages. In Norway, an employee can receive full salary for up to a year's sick leave, with the company paying the first 16 calendar days and the state covering the remainder. In Germany, a full salary is paid by the employer for six weeks and then the government assumes 70% payments for 78 weeks. In France, the government pays anywhere between 50% and 69% of a salary for one year. http://www.cfoeurope.com/displayStory.cfm/1740070 SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Can you clarify the rules a bit more? > [quoted text clipped - 50 lines] >> >> /pelle Interseting website....but, it still doesn't address whether the sick days must be consecutive or not.....or if an illness that crosses over a year-end restarts the calendar... or any other criteria covered by Norway's legislation. It could be as simple as the Employer pays for the 1st 16 sick days taken in a calendar year and the state pays the rest.....but, maybe not. *********** Regards, Ron XL2002, WinXP > Googled > [quoted text clipped - 61 lines] > >> > >> /pelle We have to pay regardelss of the illness. The employee can have a stomach ache one day and a cold for a week. It's all the same. I have this HUGE file with all days (not only planned work days) for all employees in, This includes vacation time, sick days, parenting leave work times, the works. It's probably not very practical, but I'm far from an expert.. All employees sick days are in the same list as everything else and are entered manually. On 11 Feb, 18:27, Ron Coderre <ronREMOVETHIScode...@bigfoot.com> wrote: > Can you clarify the rules a bit more? > [quoted text clipped - 48 lines] > > > /pelle That give me enough to work with that I can venture a suggestion.... With this sample structure in A1:E22 Calendar Employee Sick Day Employee Sick Day Date A. Slacker Payor B. Well Payor 1/1/2007 Sick Employer Sick Employer 1/2/2007 Sick Employer Work na 1/3/2007 Sick Employer Work na 1/4/2007 Sick Employer Work na 1/5/2007 Sick Employer Work na 1/6/2007 Work na Work na 1/7/2007 Work na Work na 1/8/2007 Sick Employer Work na 1/9/2007 Sick Employer Work na 1/10/2007 Sick Employer Work na 1/11/2007 Sick Employer Sick Employer 1/12/2007 Sick Employer Work na 1/13/2007 Sick Employer Work na 1/14/2007 Work na Work na 1/15/2007 Sick Employer Work na 1/16/2007 Sick Employer Work na 1/17/2007 Sick Employer Work na 1/18/2007 Sick Employer Work na 1/19/2007 Sick Employer Sick Employer 1/20/2007 Sick State Sick Employer Then here's one option C3: =IF(B3="Sick",IF(COUNTIF(B$3:B3,"Sick")>16,"State","Employer"),"na") Copy that formula down through C22 and from E3 down through E22 The calculated results are in the sample above. Is that something you can work with? *********** Regards, Ron XL2002, WinXP > We have to pay regardelss of the illness. The employee can have a > stomach ache one day and a cold for a week. It's all the same. [quoted text clipped - 60 lines] > > > > > /pelle The employer has to pay for the 16 days directly after an employee has become sick. The period can bridge months or years. If an emplyee falls ill the 24/12 and then gets sick again 3/1 it's within the same period. There is no upper limit to how many employer- periods an employer has to pay a year. On 11 Feb, 19:47, Ron Coderre <ronREMOVETHIScode...@bigfoot.com> wrote: > That give me enough to work with that I can venture a suggestion.... > [quoted text clipped - 101 lines] > > > > > /pelle With this structure, beginning in cell A1: Calendar Employee SickDay Date A. Chronic Payor 1/1/2007 Work na 1/2/2007 Sick Employer etc If the State pays for any "consecutive sick days" over 16 and the Employer pays for all others, then try this formula: C3: =IF(B3="Sick",IF(SUMPRODUCT(--(INDEX($B:$B,MAX(ROW()-16,3)):$B3="Sick"))>16,"State","Employer"),"na") Copy that formula down as far as you need. Does that help? *********** Regards, Ron XL2002, WinXP > The employer has to pay for the 16 days directly after an employee has > become sick. The period can bridge months or years. [quoted text clipped - 109 lines] > > > > > > > /pelle |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.