 |
 | 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 2008Is this possible please
Is this possible or am I asking too much of excel? If I have 254 working days in the year from D4 to D257 can it be done by a formula to work out the percentage automatically when a person is absent? It needs to know how many working days has passed in the year. Obviously up to today's date I believe it's 51 days. So, it would tell me for example that someone who has been off 10 days would have been off 5.1%. As each day passes the percentage would go down, or up if more time was taken off. It would finally tell me at the end of the year just how much time has been lost at a glance. If anyone has any ideas I would be most grateful. Bryan. Please clarify: is the data in D4:D257 the days that the person worked, or is there another column with 1 for present and blank/zero for absent? best wishes  SignatureBernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email > Is this possible or am I asking too much of excel? > If I have 254 working days in the year from D4 to D257 can it be done by a [quoted text clipped - 8 lines] > > Bryan. If you can compute this on paper, you can compute it in Excel. How would you compute this on paper? Tyro > Is this possible or am I asking too much of excel? > If I have 254 working days in the year from D4 to D257 can it be done by a [quoted text clipped - 8 lines] > > Bryan. Ten days out of 51 is not 5.1% but nearly 20%. You need to describe the data you have and how it is laid out, as well as what you want to do with the data, and then you might get some more concrete suggestions. Hope this helps. Pete > Is this possible or am I asking too much of excel? > If I have 254 working days in the year from D4 to D257 can it be done by a [quoted text clipped - 8 lines] > > Bryan. Thanks people, to clarify, column C2 across = name. A4 downwards = dates for every working day. Entered in each cell, 1 for every day absent, left blank for each day worked. I was working on say 100 days available for work. 100/100 *10 days absent. Probably a long way to do it. But it needs to know when the working week/year progresses to update automatically. 12 people working in total. I need to know per individual, then data then needs to be grouped together so that I know how much time lost per individual and as a group. Maybe I'm looking at it the wrong way. I read all the posts here and have learned a lot but haven't seen anything that I could adapt. Your help would be appreciated. Bryan. > Ten days out of 51 is not 5.1% but nearly 20%. > [quoted text clipped - 19 lines] >> >> Bryan. With 12 names in C2:N2, and with the first working day of the year in A4 (i.e. 01/01/2008), I put this formula in A5: =IF(WEEKDAY(A4,2)<5,A4+1,A4+3) formatted it as a date, and then copied it down to A265 to give me all the working days excluding Saturday and Sunday up to 31st December. Then I put this formula in C3: =SUMIF($A4:$A265,"<="&TODAY(),C4:C265)/COUNTIF($A4:$A265,"<="&TODAY()) formatted this as a percentage with 1 dp, and then copied this across to N3. This gives the percentage absence of the available days to date for each person under their name, and this will automatically adjust each day. For another year, you will just need to change the starting date in cell A4, and reset all the absences to blank. Of course, this implies that all the employees have been available for work all year, but if someone only started on, say, 1st February, then you would need to adjust this - possibly by having a start date above their name in row 1 and changing to a SUMPRODUCT-based formula. Hope this helps. Pete On Mar 12, 4:17 am, "Bryan De-Lara" <bryan.delara...@btinternet.com> wrote: > Thanks people, to clarify, column C2 across = name. A4 downwards = dates for > every working day. Entered in each cell, 1 for every day absent, left blank [quoted text clipped - 35 lines] > > - Show quoted text - Well Pete, it works well. I am still trying to work out what is doing what. I have the formula where you have and tried it out. It gets so far down on the dates and stops working. I'm thinking the date has something to do with this but not sure. My dates start in A4 on Jan 02 with all week-ends and bank holidays taken out, I think that works out at 254 days. I am thinking maybe I should put all these back in. If no one has a day off until March 31st then nothing registers. March 31st is D65. Is that because of the date now? Thanks again for your trouble. Bryan. With 12 names in C2:N2, and with the first working day of the year in A4 (i.e. 01/01/2008), I put this formula in A5: =IF(WEEKDAY(A4,2)<5,A4+1,A4+3) formatted it as a date, and then copied it down to A265 to give me all the working days excluding Saturday and Sunday up to 31st December. Then I put this formula in C3: =SUMIF($A4:$A265,"<="&TODAY(),C4:C265)/COUNTIF($A4:$A265,"<="&TODAY()) formatted this as a percentage with 1 dp, and then copied this across to N3. This gives the percentage absence of the available days to date for each person under their name, and this will automatically adjust each day. For another year, you will just need to change the starting date in cell A4, and reset all the absences to blank. Of course, this implies that all the employees have been available for work all year, but if someone only started on, say, 1st February, then you would need to adjust this - possibly by having a start date above their name in row 1 and changing to a SUMPRODUCT-based formula. Hope this helps. Pete On Mar 12, 4:17 am, "Bryan De-Lara" <bryan.delara...@btinternet.com> wrote: > Thanks people, to clarify, column C2 across = name. A4 downwards = dates > for [quoted text clipped - 47 lines] > > - Show quoted text - Bryan, You might like to start out with just this part of the formula in C3: =SUMIF($A4:$A254,"<="&TODAY(),C4:C254) and copy this across (format as General). It will give you the sum of days taken off for each person up to today's date, so if no time has been lost so far this year then they will all show 0. You could put some dummy data in cells earlier than today to check that it is working okay. If you open the file tomorrow, then it will be counting absences up to and including 13th March, and so on in the future. The second part of the original formula that I gave you counts the number of available days up to today's date, and the formula divides this into the number of days absent to end up with the percentage days absence up to that date. Say, for instance, that a person takes one day off in the first week, but has no other absences. Then after 10 days, that person would have 10% absence, but after 25 days this would be equivalent to 4% absence, and after 40 days it would be equivalent to 2.5% absence. If s/he then had 2 more days absence, then after 50 days the absence rate would go up again to 6%, so it is a constantly changing figure as the days pass. Hope this explains how it works. Pete On Mar 12, 4:57 pm, "Bryan De-Lara" <bryan.delara...@btinternet.com> wrote: > Well Pete, it works well. I am still trying to work out what is doing what. > I have the formula where you have and tried it out. It gets so far down on [quoted text clipped - 91 lines] > > - Show quoted text - Pete, that is a great explanation. I understand that now, I've tried it and it works fine. Thank you so much. Bryan. Bryan, You might like to start out with just this part of the formula in C3: =SUMIF($A4:$A254,"<="&TODAY(),C4:C254) and copy this across (format as General). It will give you the sum of days taken off for each person up to today's date, so if no time has been lost so far this year then they will all show 0. You could put some dummy data in cells earlier than today to check that it is working okay. If you open the file tomorrow, then it will be counting absences up to and including 13th March, and so on in the future. The second part of the original formula that I gave you counts the number of available days up to today's date, and the formula divides this into the number of days absent to end up with the percentage days absence up to that date. Say, for instance, that a person takes one day off in the first week, but has no other absences. Then after 10 days, that person would have 10% absence, but after 25 days this would be equivalent to 4% absence, and after 40 days it would be equivalent to 2.5% absence. If s/he then had 2 more days absence, then after 50 days the absence rate would go up again to 6%, so it is a constantly changing figure as the days pass. Hope this explains how it works. Pete On Mar 12, 4:57 pm, "Bryan De-Lara" <bryan.delara...@btinternet.com> wrote: > Well Pete, it works well. I am still trying to work out what is doing > what. [quoted text clipped - 104 lines] > > - Show quoted text - You're welcome, Bryan - thanks for feeding back. Pete On Mar 12, 6:32 pm, "Bryan De-Lara" <bryan.delara...@btinternet.com> wrote: > Pete, that is a great explanation. I understand that now, I've tried it and > it works fine. [quoted text clipped - 144 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.