1. 

   kaushik03
   

   Member
   Joined: Sep '11
   Posts: 776

   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

   Posted 1 year ago #
2. 

   Luke M
   

   Excel Ninja
   Joined: Nov '10
   Posts: 4,365

   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.

   Posted 1 year ago #
3. 

   kaushik03
   

   Member
   Joined: Sep '11
   Posts: 776

   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

   Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics » Excel for Beginners - Tutorials » Excel Formula Examples » Array Formulas » Excel Charts » Pivot Tables » Project Management using Excel » Excel Dashboards » Excel VBA » Advanced Excel

Our Products Full Training Programs » Excel School » Excel Dashboard Classes » Excel VBA Classes » Excel Financial Modeling » Excel for PMs Crash Courses » Excel Formula Course » Excel Dashboards Course Downloads » Excel PM Templates » Excel Formula E-book

About us » About Chandoo.org » Welcome » Best of Chandoo.org » FREE Excel Downloads » Contact Details » Excel Forums