Pete_UK
Wed Mar 12 14:05:56 CDT 2008
You're welcome, Bryan - thanks for feeding back.
Pete
On Mar 12, 6:32=A0pm, "Bryan De-Lara" <bryan.delara...@btinternet.com>
wrote:
> Pete, that is a great explanation. I understand that now, I've tried it an=
d
> it works fine.
> Thank you so much.
>
> Bryan.
>
> "Pete_UK" <pashu...@auditel.net> wrote in message
>
> news:96ddabf3-cfe0-4030-9429-ec8e3a3dac44@s12g2000prg.googlegroups.com...
> Bryan,
>
> You might like to start out with just this part of the formula in C3:
>
> =3DSUMIF($A4:$A254,"<=3D"&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
> > 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 take=
n
> > out, I think that works out at 254 days. I am thinking maybe I should pu=
t
> > 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.
>
> > "Pete_UK" <pashu...@auditel.net> wrote in message
>
> >news:91a37ff2-c0a3-430d-8168-be31c7d7656e@x30g2000hsd.googlegroups.com...=
> > 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:
>
> > =3DIF(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:
>
> > =3DSUMIF($A4:$A265,"<=3D"&TODAY(),C4:C265)/COUNTIF($A4:$A265,"<=3D"&TODA=
Y())
>
> > 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 =3D name. A4 downwards =3D=
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 togeth=
er
> > > 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 learne=
d
> > > a
> > > lot but haven't seen anything that I could adapt. Your help would be
> > > appreciated.
>
> > > Bryan.
>
> > > "Pete_UK" <pashu...@auditel.net> wrote in message
>
> > >news:O8YP9P9gIHA.1184@TK2MSFTNGP04.phx.gbl...
>
> > > > 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 we=
ll
> > > > as
> > > > what you want to do with the data, and then you might get some more
> > > > concrete suggestions.
>
> > > > Hope this helps.
>
> > > > Pete
>
> > > > "Bryan De-Lara" <bryan.delara...@btinternet.com> wrote in message
> > > >news:eMQQRi7gIHA.3352@TK2MSFTNGP04.phx.gbl...
> > > >> 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 do=
ne
> > > >> 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.- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -