 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Please help
Hi, I am trying to find a formula that allows me to know how many days an employee took as a vactaion on each month a summary sheet for each employee In order to find the number of days taken i have use the following : B22=From (date) C22= To Date D22= Total Taken C22:C44 =Official Holidays =NETWORKDAYS(B22,C22,C44:C53) Please advise Tia I've seen this, and variations of it, posted in several newsgroups - it is not a good idea to multi-post. You give no details of where your employee names are located, so are we meant to guess how your data is laid out? Pete > Hi, > [quoted text clipped - 11 lines] > Please advise > Tia Im sorry for posting so many times but i give up searching it seems that im a bad formula searcher b i dont know where to look thank you for caring I made an excel sheet to calculate the number of the vacation taken by the employees,excluding the weekend and the official holidays. every employee has his own sheet Here is the formula that i have used to know the total of days taken each time : B22=From (date) > > C22= To Date > > D22= Total Taken > > C44:C53 =Official Holidays > > =NETWORKDAYS(B22,C22,C44:C53) what i do really want now is to find formula that allows me to know the number of all the vacation requested (taken) in each month of the year as a summary sheet A5=NAME C5=JANUARY D5=FEBRUARY etc...... For each employee in his own sheet i wana make a summary for each month Thank you > I've seen this, and variations of it, posted in several newsgroups - > it is not a good idea to multi-post. [quoted text clipped - 21 lines] > > - Show quoted text - How would you want to treat the situation where the From date is in March (e.g. 28th March) and the To date is in April (e.g. 6th April), giving 6 days leave? This is actually 2 days in March and 4 days in April, assuming current year. Are your dates inclusive? Pete > Im sorry for posting so many times but i give up searching it seems > that im a bad formula searcher b i dont know where to look thank you [quoted text clipped - 55 lines] > > - Show quoted text - actually u are right but i can always divide it Example : Vacation taken =20/03/2008 till 05/04/2008 i will enter it as B22 From till =C22 Total days taken =D22 B23 20/3/08 31/3/2008 6 B24 1/4/2008 05/4/2008 4 > How would you want to treat the situation where the From date is in > March (e.g. 28th March) and the To date is in April (e.g. 6th April), [quoted text clipped - 64 lines] > > - Show quoted text - So in that case we can look at the From column to determine the month in which the leave is taken? Incidentally, are you likely to have a new workbook for another year's leave patterns, or will you continue to use this workbook? (In which case you will need more columns in your summary sheet or a way of selecting the year in that sheet - which would you prefer?) Pete > actually u are right but i can always divide it > Example : [quoted text clipped - 75 lines] > > - Show quoted text - I think its better to have it as an extra column in my summary sheet Correct me if im wrong Ex: March 07= C5 March 08=D5 right ? if is it so thats ok thank u in advance > So in that case we can look at the From column to determine the month > in which the leave is taken? [quoted text clipped - 87 lines] > > - Show quoted text - Well, if C5 is Jan 08, you would want to continue across with Feb 08, March 08 etc, so Jan 09 would be in cell O5, then Feb 09, March 09 etc. I'll have a play about and get back to you later on. Pete > I think its better to have it as an extra column in my summary sheet > Correct me if im wrong [quoted text clipped - 99 lines] > > - Show quoted text - Yes exactly Thank you for your help Waiting your reply Tia > Well, if C5 is Jan 08, you would want to continue across with Feb 08, > March 08 etc, so Jan 09 would be in cell O5, then Feb 09, March 09 [quoted text clipped - 109 lines] > > - Show quoted text - Okay, I set up a sheet called "John" with "From" in B21, "To" in C21 and "Days" in D21, and then put some dates in B22:C22 with appropriate days in D22, and repeated this on a few more rows. I copied the sheet a few times and renamed the copies to "Mary", "Fred" etc and set up some more test data. In a sheet called "Summary", I put "Name" in A5, with "John", "Mary", "Fred" etc down the column. and then in C5 I put the date 1/01/08 and used a custom format on this cell of mmm yy to display as Jan 08. Then I put this formula in D5: =DATE(YEAR(C5),MONTH(C5)+1,1) and formatted that as mmm yy so it displayed as Feb 08, and then copied this cell across to give me sequential months. You can change the start date in C5 to suit the period of time that your data covers. I put this formula in C6: =SUMPRODUCT((MONTH(INDIRECT("'"&$A6&"'!B22:B50"))=MONTH(C $5))*(YEAR(INDIRECT("'"&$A6&"'!B22:B50"))=YEAR(C$5))*(INDIRECT("'"& $A6&"'!D22:D50"))) and then copied this across and down as appropriate - this returned the number of days leave for each person in each month, so hopefully this is what you wanted. If you have data beyond row 50 for any one of your employees, then you will need to amend the ranges appropriately - there is no problem if you make them much larger, but the formulae might take longer to calculate. Hope this helps. Pete > Yes exactly > [quoted text clipped - 117 lines] > > - Show quoted text - U are actually right but i could treat this situation by the following action (i will always seperate 2 months or 2 different year in my data entry) Example Vacation date from 25/03/2008 till 05/04/2008 i will do the following: B22 From C22= To D22= Total taken 25/03/2008 31/03/2008 5 days 01/04/2008 05/04/2008 4 days > How would you want to treat the situation where the From date is in > March (e.g. 28th March) and the To date is in April (e.g. 6th April), [quoted text clipped - 64 lines] > > - Show quoted text - Tia, I did offer you the workbook I have already done for holidays, minus week-end and bank holidays. Bryan. U are actually right but i could treat this situation by the following action (i will always seperate 2 months or 2 different year in my data entry) Example Vacation date from 25/03/2008 till 05/04/2008 i will do the following: B22 From C22= To D22= Total taken 25/03/2008 31/03/2008 5 days 01/04/2008 05/04/2008 4 days On Mar 26, 4:30 pm, Pete_UK <pashu...@auditel.net> wrote: > How would you want to treat the situation where the From date is in > March (e.g. 28th March) and the To date is in April (e.g. 6th April), [quoted text clipped - 71 lines] > > - Show quoted text -
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.