Show Leave Days and workdays from multiple inputs field
- Thread starter Dackson
- Start date
Sajan
Excel Ninja
Hello Dackson,
Welcome to the forum.
I took a look at your sample file, but could not understand what you are after.
What are EDAY and SDAY? Folks outside of your industry may not be aware of acronyms used in your industry. If understanding them is important for the solution, please describe what they are.
Please describe your problem in more detail. I would encourage you to add some manual examples to illustrate what you are after.
-Sajan.
Welcome to the forum.
I took a look at your sample file, but could not understand what you are after.
What are EDAY and SDAY? Folks outside of your industry may not be aware of acronyms used in your industry. If understanding them is important for the solution, please describe what they are.
Please describe your problem in more detail. I would encourage you to add some manual examples to illustrate what you are after.
-Sajan.
Dackson
Member
Hi Mr.Sajan,
I want to enter only date or day that green color field. SDAY - Leave Starting date & EDAY - End date. First c5,e5;c6,e6;c7,e7 entry section for Leave Starting From date to Leave End date( ex from 02/09/2013 to 12/09/2013 leave), next entry section h5:k7 for single leave days (ex 17/09/2013 only for one day). when i will change in entry field in the 2nd row data P or L want to change automatically through the condition.
I hope its clear now...
Thanks your patient
Dackson Jose.
I want to enter only date or day that green color field. SDAY - Leave Starting date & EDAY - End date. First c5,e5;c6,e6;c7,e7 entry section for Leave Starting From date to Leave End date( ex from 02/09/2013 to 12/09/2013 leave), next entry section h5:k7 for single leave days (ex 17/09/2013 only for one day). when i will change in entry field in the 2nd row data P or L want to change automatically through the condition.
I hope its clear now...
Thanks your patient
Dackson Jose.
Sajan
Excel Ninja
Hi Dackson,
I am still not understanding what you are after.
Instead of me trying to decipher your worksheet, can you describe what you are trying to achieve? That will ensure we are formulating a proper solution that addresses all of your requirements.
The following is what I have understood so far, but there are many gaps in my understanding.
-Sajan.
I am still not understanding what you are after.
Instead of me trying to decipher your worksheet, can you describe what you are trying to achieve? That will ensure we are formulating a proper solution that addresses all of your requirements.
The following is what I have understood so far, but there are many gaps in my understanding.
- You are looking to track the "leave" days and "work days" for people
- Are you looking to track this for just one month, or multiple months?
- As input, you will have the specific dates when folks will be on "leave" (assuming that they are working on all other days)
- You want to see those "leave" dates and "working" days visually represented, in aggregate. (i.e. this display is not per person.)
- You also want to see the total number of "leave" days and total number of "working" days. Is that by month, or aggregate across all months?
- What else are you trying to do?
-Sajan.
Dackson
Member
- You are looking to track the "leave" days and "work days" for people - Yes (people not important - any one)
- Are you looking to track this for just one month, or multiple months? - Single month September only.
- As input, you will have the specific dates when folks will be on "leave" (assuming that they are working on all other days) Yes
- You want to see those "leave" dates and "working" days visually represented, in aggregate. (i.e. this display is not per person.) ok
- You also want to see the total number of "leave" days and total number of "working" days. Is that by month, or aggregate across all months? ok
- What else are you trying to do? Only i wish changes Blue background data.
Dackson
Member
Yes........ I want to changes only in the blue background. Try your level best. I have trust with u. Definitely you will clear this problem for me.
Dackson Jose
Dackson
Member
Hi Sajan
For More clarification
http://forum.chandoo.org/threads/get-leave-days-and-work-days-of-an-employee.12242/
For More clarification
http://forum.chandoo.org/threads/get-leave-days-and-work-days-of-an-employee.12242/
Sajan
Excel Ninja
Hi Dackson,
Check out the attached file... I have added in some formulas based on my understanding of what you are trying to do. I have added a worksheet called "Restructured".
I have added two named references: "StartDays" and "EndDays"
Please ensure that these named references refer to the complete data ranges. Once you confirm that this format works, we can make them dynamic, if needed.
The worksheet expects you to input the start and end dates (as valid dates) for time periods when people take leave.
The Totals area shows the total leave for a month, remaining working days for the month, and a pictorial representation showing the days that people are on leave vs people are working.
Cheers,
Sajan.
Check out the attached file... I have added in some formulas based on my understanding of what you are trying to do. I have added a worksheet called "Restructured".
I have added two named references: "StartDays" and "EndDays"
Please ensure that these named references refer to the complete data ranges. Once you confirm that this format works, we can make them dynamic, if needed.
The worksheet expects you to input the start and end dates (as valid dates) for time periods when people take leave.
The Totals area shows the total leave for a month, remaining working days for the month, and a pictorial representation showing the days that people are on leave vs people are working.
Cheers,
Sajan.
Sajan,
You can simply use COUNTIF in B15 & C15, since you have already "L" & "P" returned in the range.
Also, another formula in D15 to across & down with COUNTIFS,
=IF(D$14<=DAY(EOMONTH($A15,0)),IF(COUNTIFS(StartDays,"<="&$A15+D$14-1,EndDays,">="&$A15+D$14-1),"L","P"),"")
You can simply use COUNTIF in B15 & C15, since you have already "L" & "P" returned in the range.
Also, another formula in D15 to across & down with COUNTIFS,
=IF(D$14<=DAY(EOMONTH($A15,0)),IF(COUNTIFS(StartDays,"<="&$A15+D$14-1,EndDays,">="&$A15+D$14-1),"L","P"),"")
Sajan
Excel Ninja
Hi Haseeb,
Good formulas!
I had come up with the calculation for B15 and C15 before I worked on the pictorial representation, and did not check back whether all of the formulas were still needed. Thanks for that optimization.
I like the COUNTIFS solution since it is more intuitive. (I just have to remember to use the *IFS formulas more!)
Regards,
Sajan.
Good formulas!
I had come up with the calculation for B15 and C15 before I worked on the pictorial representation, and did not check back whether all of the formulas were still needed. Thanks for that optimization.
I like the COUNTIFS solution since it is more intuitive. (I just have to remember to use the *IFS formulas more!)
Regards,
Sajan.
NARAYANK991
Excel Ninja
Hi Sajan ,
I would like to comment that the original workbook uploaded by Jose had a drop-down for selecting a particular employee , and all the calculations pertained to that employee.
Your formulae are for all employees taken together. May need to be revised.
Narayan
I would like to comment that the original workbook uploaded by Jose had a drop-down for selecting a particular employee , and all the calculations pertained to that employee.
Your formulae are for all employees taken together. May need to be revised.
Narayan
Sajan
Excel Ninja
Hi Narayan,
We can certainly add drop-downs, etc. once Dackson confirms that the format and calculations work.
In his response to my questions, Dackson offered the following:
Also, see response to #4 above.
We will wait to hear back from Dackson to determine any adjustments needed.
-Sajan.
We can certainly add drop-downs, etc. once Dackson confirms that the format and calculations work.
In his response to my questions, Dackson offered the following:
Also, see response to #4 above.
We will wait to hear back from Dackson to determine any adjustments needed.
-Sajan.
Dackson
Member
Hi Sajan & Narayan
Thanks your innocent effort, I am always liable for that........
Not hurry take your own time but try to follow conditions. One more think for your kind attention ("DD") day only my in put data not a date. Bcoz I already mentioned current Month and Year, find in cell L4, P4.
I hope both will achieve soon for final answer.
Thanks your innocent effort, I am always liable for that........
Not hurry take your own time but try to follow conditions. One more think for your kind attention ("DD") day only my in put data not a date. Bcoz I already mentioned current Month and Year, find in cell L4, P4.
I hope both will achieve soon for final answer.
Dackson
Member
Hi saja,
We can avoid those multiple month or multiple employees’ information. We can conclude this for single employee sheet for current month. It’s fine for me.
· 1.Please considered continue leave and single day leave.
· Kindly took for calculation “DD” day from input.
· Kindly look after for report summary.
Thanks for your kind effort.
We can avoid those multiple month or multiple employees’ information. We can conclude this for single employee sheet for current month. It’s fine for me.
· 1.Please considered continue leave and single day leave.
· Kindly took for calculation “DD” day from input.
· Kindly look after for report summary.
Thanks for your kind effort.
Sajan
Excel Ninja
Hi Dackson,
I am not sure I understand what you said above. What is "DD"? Do you mean that the date is two digits, or something else?
I see that you have the month name as text in L4 and year in P4. Are you asking that the day input be just numbers, and assumed to be for the month and year indicated in L4 and P4? Is there any reason why you cannot supply valid dates as input?
When you say report summary, are you referring to the pictorial representation (A2:AE2) or the area where you have the dates listed out (V5:AD9)?
Are you wanting to have the dates from C5:E7 written out as dates in the range V5:AD9?
Is there any reason why you would not list the single leave days using the same format as the continuous leave days? Why the format that looks like a puzzle? Is there any significance of having the values in H5 and K6 with spaces in between? How would you capture the leave for an employee that takes more than 12 single day leave?
I am sure you like your input form, but are there any constraints on your input form that would not allow you to consider new formats for input?
-Sajan.
I am not sure I understand what you said above. What is "DD"? Do you mean that the date is two digits, or something else?
I see that you have the month name as text in L4 and year in P4. Are you asking that the day input be just numbers, and assumed to be for the month and year indicated in L4 and P4? Is there any reason why you cannot supply valid dates as input?
I am not sure what you are asking here. The calculations already take into account leave that spans multiple days vs a single day.
When you say report summary, are you referring to the pictorial representation (A2:AE2) or the area where you have the dates listed out (V5:AD9)?
Are you wanting to have the dates from C5:E7 written out as dates in the range V5:AD9?
Is there any reason why you would not list the single leave days using the same format as the continuous leave days? Why the format that looks like a puzzle? Is there any significance of having the values in H5 and K6 with spaces in between? How would you capture the leave for an employee that takes more than 12 single day leave?
I am sure you like your input form, but are there any constraints on your input form that would not allow you to consider new formats for input?
-Sajan.
Dackson
Member
Hi,
Its very interesting to communicate with Chandoo members like a long deeply friends. Wonderful..... Sorry I am very late for catch my good friends...
As per my concept I want to apply input data very minimum. Excel given to us lot of formula and lot of tricks. We have to utilized the option from excel and given to good output to user. I have one more concept that is avoid maximum user clerical error. Then user will very happy and we will get good achievement with more satisfaction.
Your suggestionfor input Dack's input
Enter date: 01/09/2013 1
One day leave entry From 01/09/2013 to 01/09/2013 1 (In single leave field)
One employee if take alternative days leave 16 days maximum leaves days in a month and less the weekend day that way I given here 12 days in single entry field.
I would appreciate your positive gesture on this matter.
Thanks & regards
Its very interesting to communicate with Chandoo members like a long deeply friends. Wonderful..... Sorry I am very late for catch my good friends...
As per my concept I want to apply input data very minimum. Excel given to us lot of formula and lot of tricks. We have to utilized the option from excel and given to good output to user. I have one more concept that is avoid maximum user clerical error. Then user will very happy and we will get good achievement with more satisfaction.
Your suggestionfor input Dack's input
Enter date: 01/09/2013 1
One day leave entry From 01/09/2013 to 01/09/2013 1 (In single leave field)
One employee if take alternative days leave 16 days maximum leaves days in a month and less the weekend day that way I given here 12 days in single entry field.
I would appreciate your positive gesture on this matter.
Thanks & regards