Dear all,
In my file,in sheet "data" contains (annual leaves-Absences-Substitutions) of 147 employees in every month.
1-I want to export the data of each item (annual leaves-Absences-Substitutions) in "Data" sheet to its specified sheet.
2-I want to create a formula in sheet "Data" column "c" to get balance of anuual leaves only
Note: Default annual leaves :21 days
Find the attachemnt
This looks like it is do-able. However, I have some questions.
On line 3 (Employee A) there is a 06+08 in column D. Does this mean that he used leave on the 6th and the 8th (two days) and that his leave balance is 19 (21-2) at this point? Also , can we always depend on a plus sign (+) being there for more than one date?
Also do absence, substitution and instead of factor into leave balance and if so, how?
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Hello leprince2007,
dflak is right. You definitely need to clarify the entry syntax. We cannot help you if we don't understand what you need.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
This version does the parsing of the data into the separate sheets.
I'll await the answer to the question before computing remaining leave.
Great jobOriginally Posted by dflak
Thank you for your answer.that is what i want.
I want a formula to calculate remaining annual leaves only.and ignore absence and substitutions
I write annual leaves as dates separated by "+"sign if the cell contains more than one date.
"06+08" they are 2 days >> remaining annual leaves are(21-2)=19 days
Thanks in advance
Last edited by leprince2007; 01-04-2017 at 06:21 PM.
Here's the version with the formula for computing leave remaining
=21-GetDays(D4)-GetDays(H4)-GetDays(L4)-GetDays(P4)-GetDays(T4)-GetDays(X4)-GetDays(AB4)-GetDays(AF4)-GetDays(AJ4)-GetDays(AN4)-GetDays(AR4)-GetDays(AV4)
GetDays is a user-defined function that counts the number of plus signs and adds 1 or it returns 0 if the cell is blank.
I did very little QA on this, so I suggest you check it out for yourself.
Thank you for reply.But your formula is very big.why don't you use a normal formula??
It's a long formula, but a simple one. I did it the way I did because of the way the data are laid out. The range of values is not contiguous, so that cuts down on some options.
I did give thought to writing a UDF that would look at all the cells internally, but decided that this approach is more flexible. There is no hard coding or assumptions about how the data is laid out. You can use the GetDays formula on any cell.
Can I close this post and create a new one in formulas section in this forum??in order to find anybody to help me in this formula as I need a normal formula.
Hello leprince2007,
Marked this thread as Solved. You can then post your formula question in the Excel Formula and Functions forum. Add a link back to this post for reference if needed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks