• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Leave Tracker_Need urgent Help

kaushik03

Member
Hi there....


I have designed an userform to update a leave tracker.The input date (on the form) is getting saved on some other sheet in the following manner.


Employee_Name (column A1) Reporting_Manager(column B1) Type_of_Leave(column C1) Leave_Period_Start_date (column D1) Leave_Period_End_Date Comments(column E)


Each rows of these columns are getting filled one after another as the users filling up the form


Now I want to prepare a kind of an interactive dashbord which tells the manager how many resources are on leave/how many resources are available for a perticular period(manager should be able to select any date range) and which are the dates on which a person has taken leaves(however, we have only Leave_Period_Start_date and Leave_Period_End_date) as the input data)


Please post your thoughts if you have any suitable suggestions....


Kaushik
 
For any time period question, you'll need to use some formula to determine overlaps. I think Chandoo has written a few articles on how to determine is something overlaps.

http://chandoo.org/wp/2010/06/01/date-overlap-formulas/


This should then let you do some sort of COUNTIF or SUMIF statements. TO figure out which dates the person has taken, you could have a list of all dates between the criteria ranges and then use the overlap formula to determine which ones the person was gone.


Or, simplest to setup, but maybe not displaying everything you want, would be to do a PivotTable. Could easily choose which employees to look at, how many times they're gone on leave, etc.
 
Hi Luke..


Thank you for your quick reply..


Yesterday I was trying to put some efforts to solve the problem and I came up with a resolution.I was able to present the information by preparing an interactive chart.The chart comprises of the list of employees(20) on X- axis and the number of days they have applied the leaves for are on Y axis.

Fisrtly, I made the columns dynamic by using "Offset+Counta" formula(dynamic named ranges).Then using the sumproduct formula I was able to get the number of days a person has applied leaves for a perticular period. I have given the user an option to select the start and end date to get the chart automatically updated.


Afterthat, by applying an "if vlookup" formula I extract the start date and end date of the persons who are updated on the chart for a certain period selected by the user.


I understand that it sounds little complecated(without viewinmg the data).But the reason I am telling you this I have been facing another challenge for extracting some other information from the dtata, for which you might want to see the entire spreadsheet along with my query/question so that you can understand what exactly I am trying to get.Please let me know how can I send you my workbook(obviously with some dummy data) so that you can help me out...


Thank you for your time....


Kaushik
 
Back
Top