Report Of Employees By Date, Holidays & Sick Days

  • Hi,

    Not sure what to put as my thread title, if someone can change then please do so.

    Please see attached spreadsheet on what I am trying to achieve.

    In the data tab I have my base data, which is dates across the top and names down the side. I have information in the middle such as 'H8' which means that the person was on holiday for 8 hours on that particular day, and 'S8' which means they were sick for 8 hours on that day.

    I want a macro that will rearrange the information in the table and put it into the report format which is in the report sheet.

    If you see the spreadsheet you will probably get a better idea of what I am trying to achieve.

    Thanks and please ask any questions.


  • Re: Lookup Dates And Rearrange Adjacent Data

    Without sounding rude, you're giving yourself a major problem with that data layout.

    Have a look at the attached. You'll need far fewer posts on this forum if you lay it out like a proper database! :)

    edit: made a few tweaks to get closer to your desired report

  • Re: Report Of Employees By Date, Holidays & Sick Days

    Hi Yard,

    Thanks for your reply, and what you have done works well!

    I know the layout of the sheet is bad, but this is the company format for the holiday planner, which this is for.

    Is there a way of getting my format into yours with a macro or running the pivot table straight from my format? (Bearing in mind there will be a lot more employees and the dates cover a year going across the top.



  • Re: Report Of Employees By Date, Holidays & Sick Days

    Hi MJB123,

    I've quickly knocked up something that should get you somewhere near where you need to be...

    The sheet (attached) now has a userform to select the employee to run the report for, a command button on the data sheet to start up the macro, a report template page to copy the format for each individual and a key sheet which relies on the following;

    All codes for possible absence types listed.
    All codes are only one letter long.
    The order of the codes is the same as they appear in the columns on the report.

    here's the code;

    and on the userform

    First it summarises each nonn-blank column for the employee into a data record, then it puts that on a fresh template.
    Finally it then scans thorugh what it has just done and combines any records on consecutive days of the same absence type.

    Hope that Helps!
    Feel free to let me know any issues


  • Re: Report Of Employees By Date, Holidays & Sick Days


    I have come across an error in the code.

    I have extended the range of dates to look at to 365 columns, 1 for every day of the year.

    If I have a date which is at the end of the year (i.e end of the holiday year, 31 Mar 09) when the code is run it adds 1 to whatever is put down as the amount of hours for the leave.

    Example, if I put 'H8' in for 31 Mar 09, then on the report it shows that day as 9 hours annual leave???

    I've tried going through the code but can't see where it is doing this.



  • Re: Report Of Employees By Date, Holidays & Sick Days

    There's a slight error in the code working out the number of's using the date rather than the info on the employee just so happens that the formula applied to a 2008 date = 8 hours but applied to a 2009 date = 9 hours which is where the error was coming from.

    Updated section below;

    x = x + 1 
                AbRec(0, x) = KeyMap(1, d) 
                AbRec(1, x) = Sheets("Data").Cells(1, b).Value 
                AbRec(2, x) = Sheets("Data").Cells(1, b).Value 
                AbRec(3, x) = Int(Right(Sheets("Data").Cells(EmpRow, b).Value, _ 
                Len(Sheets("Data").Cells(EmpRow, b).Value) - 1)) 
                AbRec(4, x) = d


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!