Counting instances in a period

O

Oslopelle

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
 
R

Ron Coderre

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
 
O

Oslopelle

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...
 
D

Don Guillett

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
 
O

Oslopelle

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.
 
R

Ron Coderre

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
 
R

Ron Coderre

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
 
O

Oslopelle

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.
 
R

Ron Coderre

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
 
Top