Hello,
I'm currently calculating sickness absence for my company, I've created a data entry form and want it linked to a overview by month. E.g. John Smith has been absent between 01/04/15 - 30/09/15 which equates to 131 working days, I'd like this broken down by days lost per month. I've attached the example excel sheet.
Thanks in advance.
Joe
The easiest way is to convert your data entry to include all the days, then use a pivot table. It would also be better if you added a list of Holidays for all the years covered, named "Holidays".
Try this file, with the macro needed.
Absence Calc.xlsm
Bernie Deitrick
Excel MVP 2000-2010
In your sample file Overview I replaced the months with the first of the months. To make the end of the year "wrap around" it starts at Oct 1, 2015 then at Jan it starts at Jan 1, 2016. Then in B3:M3 this formula.Formula:
Please Login or Register to view this content.
Dave
I considered a formula like yours but was concerned that there may be multiple rows of entries for each person, covering different time periods. Did you come up with a solution for multiple entries?
@ Bernie Deitrick
Strange you should ask. I was just working on that. Haven't been able to come up with anything that doesn't promise to be a cumbersome mega-formula. Ugh! Also don't know how to total them in the summary yet.
Also due to the year-end-wrap-around I've considered proposing a different layout for the Overview sheet ... something that uses contiguous month / years in the row headers.
Last edited by FlameRetired; 11-11-2015 at 02:50 PM.
Thanks for your help so far, really appreciated. Thats the thing, the company i'm doing it for will have multiple entries per employee.
My solution will work for multiple entries per employee.
This is a formula solution. It will work for multiple entries of each name, but it will not combine the results on one line in Overview sheet. I.E. if there are 2 entries for Anna Jones there will be 2 separate returns for Anna Jones in Overview. As a workaround there is a SUBTOTAL above the summary (row 1) that works with Autofilter so you can get a summary of any multi-listed name.
This first formula just transfers names from Entry to Overview in same order. If you change the name order in Entry it will automatically adjust in Overview.Formula:Please Login or Register to view this content.
Then this formula in B3 of Overview filled down and across.Formula:Please Login or Register to view this content.
Hope this helps.
Last edited by FlameRetired; 11-13-2015 at 12:29 AM. Reason: clarity / typos
Bit of a delayed reply to this thread, but would anyone know if it's possible to amend the formula to exclude bank holidays? This would be linked to a list of dates.
JoeMac89,
Which do you want amended? Bernie Deitrick's macro solution or mine?
Yours if that would be possible FlameRetired?
It has been awhile since I have looked at this. If memory is serving me this should work.
I put a list of holidays that fall in the range of 'Entry' dates in K2:K13 of 'Entry'. I named it Holidays.
The formula in B3:M7 'Overview' is nowFormula:Please Login or Register to view this content.
I have to admit to being relieved when he asked for your version and not mineOriginally Posted by FlameRetired
@ Bernie Deitrick
I have to admit to being relieved when he asked for your version and not mineLOL .... I don't blame you.
Hahaha.. appreciate your help with this guys!
You are most welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks