Medical leave spreadsheet using rolling calendar year dropping hours used as they roll off

[Celmore]

Hi there, I am new to this post. I have searched to see if there was a solution already to my issue but I didn't see. I work for a company that employees over 500 employees. I take care of FMLA , family medical leaves. This is a government program with lots of regulations in case you aren't very familiar with it. This is a massive endeavor with so many employees. I have inherited this duty and right now unfortunately it is a paper process! I am desperately seeking help to make this a somewhat automatmated process. We use a rolling calendar year. Employees can use FMLA in increments of 15 minutes! Each employee has 480 hours (12 weeks) of medical leave available in a rolling calendar year. Government regulations require it be tracked and I also have to notify via certified letters when they are close to depleting leave time and let them know how much time they have used and when it will expire. What I have to do is document name, ID number, supervisor, date, medical issue (example: surgery, care of parent, birth of child, etc). Can anyone help me with a spreadsheet that can include info noted previously but also show me based on rolling calendar year how much each person has used and how much time is available and set up an automatic alert to help me keep up when they get to a certain level to sent certified letter to them?

I dont have current spreadsheet to send as I am just know working on this project. With so many employees I wasn't sure the best way to set up as it will be massive. Right now I have 200 or so employees that have or have had FMLA issues!

Any help, direction would be appreciated. I think my biggest challenge will be getting a formula set up to calculate as a rolling calendar year as I am not that great at setting that sort of thing up.

Thanks to all.

[Green Crocodile]

Can you please create a sample spreadsheet of how you would like the layout to be? How you will enter the data e.g.(How you would enter the hours. Will you have the person listed once and just add to the hours they use, or will you enter the person again if they use it for a different date)? Can someone use say 100 hours in the first 1 month, then seven months later use another 50, or will it have to be consecutive usage, etc?

If you can also please include maybe a sample scenario, like John Doe, Supervisor Jane Doe, Date etc, hours used, reason, and expected results, and highlight the fields that need auto calculation.

Maybe something like the attached? Just created this since I was bored and had time on my hands.

[Celmore]

Thank you for your response! Yes I will put some things together tomorrow at work and send tomorrow evening. Here is alittle more info. Are you familiar with Family Medical Leave?

Yes you have the right idea in your spreadsheet. FMLA can be used as needed which means all 12 weeks can be used at one time or it can be used intermittently. It can be for multiple medical conditions for one person! Example is a person goes out 8/17/16 for 6 weeks with a broken leg. They return to work and work a month and they have anATV accident and the doctor puts them out 3 weeks. They return to work again. Then they have to go out again because they have a parent to care for. They use 2 weeks. So they have 1 week left. In the mean time this person happens to have on file already for a chronic condition that will never go away and occasionally they have flare ups and are unable to work. This is an intermittent leave and could use in increments of 15 minutes. So one day they may call in and use 8 hours for this condition then a few days later they have to leave early from work and 1 1/2 hours were used and so on. So say in the next 3 weeks they exhaust the remains of their medical leave sporadically due to their intermittent FMLA for their chronic condition. Now this person has no more medical leave time available until 8/17/17. So on 8/17/17 they will gain a day back and the next day they will gain another day and so on. As they gain their days they can start using again if needed.

Each employee has maximum of 12 weeks (480hours) available in a rolling calendar year. It does not matter how many conditions they have on file if they exhaust the 480 hours within the rolling calendar year, they have no more medical leave time available until it starts rolling back on..

The hours used for each condition is recorded separately so the person with multiple conditions would need to be listed more than once on spreadsheet.

Thanks again for your time but I will send attachments tomorrow. Maybe the above info might help to understand alittle bit more!

[TheN]

so, they have 12 weeks worth of time off, and they regain that time off exactly one year from when they use it?

It doesn't seem like that should be too hard to set up.

I'd suggest a "master list" in one tab, and then an "input list" in another tab. Have all the formulas in the master list. If you set it up like that, you can also have a running total of how often they use it per month/quarter etc.

I also saw this:

Twenty-six workweeks of leave during a single 12-month period to care for a covered servicemember with a serious injury or illness if the eligible employee is the servicemember’s spouse, son, daughter, parent, or next of kin (military caregiver leave)

So you might have to account for that at some point in time. Just a heads up.

[Celmore]

Hello There..thank you for responding. I really appreciate your time in looking into this.

Yes you are correct on the Military Leave. It is part of the government mandated Family Medical Leave Act.

The leaves that I am dealing wih are all related to medical. FMLA (12 weeks/60 workdays/480 hrs-available
after employed 1 year), General Leave (4 wks/20 workdays/160 hrs-company supplied after 90 days
employed to get employees through until eligible for FMLA), Military Leave (26 wks/130 workdays/1040 hrs),
Maternity Leave (16 wks/80 workdays/640 hrs-TN has a maternity law that is 16 weeks), Workers Comp
(same as FMLA, if there is a workers comp injury with time out of work the time counts against their 12 wks
of FMLA). There is also a code of VacFMLA-this is same as FMLA 12 weeks. FMLA protects jobs but it is unpaid.
Employees may elect to use vacation time at the same time as FMLA in order to get a paycheck and to pay their
premiums.

Right now medical leaves are tracked manually on an attendance card. Due to the regulations surrounding
FMLA, it is very time consuming. I'm hoping someone can help me create a spread sheet to easily manage it.

Medical leaves can be used at a minimum of 15 minutes up to 12 hours. We have 8, 10 and 12 hour shifts.
Medical leaves may be continuous or intermittent depending on the individuals condition. They can have
multiple issues on file at one time but only a maximum of 12 wks (or 16 if maternity or 26 if Military) available in
a rolling calendar for the employees issues.
Rolling calendar is looking back from current date 1 year to see what has been used. Time will roll
back on one year later from the date they used.

I pull hours recorded as medical leave out of kronos, our timekeeping system. Unfortuately, the general
reports that are available are not the best for this but I did find one that could be used. It has a lot of accounting
information on it that I delete off and just leave what I need. It has name, id, date,day of wk, type of medical (FL,
GL, M, etc) , hours used, days used. Kronos actually has a FMLA program but unfortuately the versus we have
does not include it.

I will use the kronos pull to record the hours used into a spreadsheet. I am wanting the spreadsheet to help me
keep up with time used, time available, alert when notifications would need to go out. I'm thinking when
employee has 80 hours left would be okay as a reminder to send out certified letter with required info.

I hope this makes sense. I'm sure there is info that I have forgotten but maybe this is enough to be useful.

I am attaching some documents. I hope they go through but if not I will resend if needed.

The first attachment is the current manual process..it is horrible! It is individual attendance cards for each
employee. Since it is a rolling calendar year there are 2015 and 2016 cards to keep. They are all in a notebook
that is about 3 1/2 inches. They are jam packed in there! It is a huge book! The front of the card has hours
used each day and on the back it has the medical issues covered.

The second attachment is an example of what I pull out of kronos showing hours used.

The third attachment is my attempt to create a spreadsheet…not good at all…It is too spreadout and not
and not user friendly at all. Since some issues are ongoing, I also have a column labeled as recertify. We will
keep the medical issue on file for the time frame list within the doctors paperwork. At expiration, new paperwork
will have to be completed.

The fouth and fifth are examples of what I found online. Im not crazy about the one that would be a separate
sheet for each person. That seems to hard to manage with such a large number of employees. The last one
I found interesting but would need more areas and it uses codes and I really need to see hours/days.

Maybe someone will have a better idea on this….I sure hope so…I appreciate any help provided!
Please let me know if anyone has any questions to clarify..thank you

[Celmore]

The attachments did not go through...so I am trying again...

[Celmore]

Here is the last one.....

[dmg2016]

Hi Celmore,

I recently went through the process of creating a report very similar to what you are looking for that relied on a kronos download. I will try to post a template that will hopefully work for you later tonight. Is "Vac FMLA" the only paycode that you are interested in from kronos? Also, are you set on using a gantt chart to display when people have of?

DMG

[Celmore]

Hi there, I look forward to seeing your templete. The actual Kronos codes I use are FL (FMLA & Maternity) GL (General Leave) M (Military), VacFMLA (Vacation & FMLA), WC (Workers Comp).

I'm not sure about the question about the gnat chart. Any easy efficient way to manage FMLA I am for it. It is new to me within the last 3 months and it has been overwhelming! Any process, tips, insight to manage this I will be so grateful! Thank you

[dmg2016]

Celmore,

I am working on getting a template that I can post, so it maybe another day or two before I get it up here. The one I created has about 50+ named ranges/formulas, vba, and links to external workbooks. The spreadsheet took close 2.5 months to build because I had to spend a lot time learning how to use array formulas and vba.

I started off in very similar way as you did it seems. I went deconstructed a number labor-force planning templates to get an idea how to setup the spreadsheet.

Layout:
Control Sheet: I started out with a control with all inputs/constants that would drive the rest of spreadsheet. In your case, I would put things such as the pay code, and pay code number of available hours for FMLA (such as 480). I would also have a setting on this sheet to determine how many hours before you received a warning (80 hours left). Maybe set the person's name as yellow, and Then we they get even closer it would turn red.

Employee List: I then went and created a unique list of the employee having the all information related to that employee (name, id, title, dept, supervisor, start date, etc...).

Output/Summary Sheet: On this sheet, I had a list of the employee again (I had to separate the employee list but your may want to combine it with the employee list sheet) that had the original balance, sumifs formulas based on the kronos paycode, and then the balance of hours remaining. To capture the rolling aspect of the calendar you would use within the sumifs formula ">="&edate(today(),-12) as a criteria and the kronos date column as the sum range.

Employee Search: This was vba tool I had mine, that allow me to put up an employee to see the days that they had taken off so far and then edit if something was off.

Monthly Calendar/Daily Calendar: I also had a vba calendars that you could display when employees had off based on a calendar view.

I attached a very rough template/foundation so you could get a little better idea of what I was talking about above. I will still try on getting a much better one posted later in the week.

DMG

[Celmore]

Hi there, wow DMG looks and sounds like you did spend a lot of time working on your process. Do you have your process where you want it to be and working well for you?

It looks a little overwhelming. It looks promising if I can wrap my head around it though. I will be able to examine the templete more this weekend. Thank you for your help.

[Celmore]

Hello...Green Crocodile...here are some attachments that I messaged you about...

The Medical Leaves is what I was trying to come up with to manage medical leaves.

The Kronos pull is where I get the hours/days used out of our time keeping system. The pull is not user friendly. There is a lot of accounting information on the report but I delete everything except for the few items I can use. The days are not on there. I have it convert it to days. Then I have been taking this and entering it on the medical leaves spreadsheet.

let me know if you have any other questions....thanks so much

[Green Crocodile]

Hi Celmore,

Thanks for the attachments. From what I understand, this is a fairly simple concept. Ive done a few similar projects for ICD 10 from 9 transitions, and developed systems for CMS, and DHCS. I have never done FMLA, but i believe it uses a similar logic.

Can you please tell me if Kronos is SQL driven or NO-SQL (mongodb?).

Also, I am a little confused about how can thry cap at 12 weeks if military gives more?

I really would like to understand this and create a template that can serve to assist with automation. I would like to. Post the template here for the public as well as my website.

[Celmore]

Hi there, sorry for the late reply.. I don't know the answer to the Kronos question. I am sorry. Is there an easy way to check?

Yes, normal FMLA is 12 weeks and military is 26 weeks. I am not sure why but I would say it is because they are service men and women serving our country. It also covers certain family members of the service person at times of deployment, care for of injured service person etc.

Different states have laws that override the FMLA law. My state of Tennessee has a maternity law that allows 16 weeks fir pregnancy. Mother and father are both entitled to this leave time.

The department of labor website has all the regulations (dol.gov) of FMLA. It is massive! If you have a chance to take a look at the website it may make more sense. There are forms that must be completed by employee, employer and medical Doctor and explained with the employee so they fully understand there rights and responsibilities. There are specific time frames for the documents to get to back to employer and time frames to notify employee if they are approved or not and also notifications must be sent informing how much time they have used and what they have left.

The most overwhelming thing for me to get a handle on is my company has so many employees that have to use it and use it for multiple issues. It is hard to keep up with when people are getting close to depleting all their time and making sure all notifications are sent. Unfortuately many employees lose their job because they run out of leave time so you have to make sure you do everything correctly and have all documents, notifications, etc on file. You definitely have to stay on top of it!

Thanks for the time and if their are any other questions please let me know...

[Celmore]

Hello, i wanted to see if anyone had any further input regarding managing medical leave if absences? Still searching for a managible way...thanks for everyone's time and responses so far

[HyderUA]

This is exactly the type of tracker I've tried to create, but have not been able to do. Were you able to get a working tracker?