Is this possible please

  • Thread starter Thread starter Bryan De-Lara
  • Start date Start date
B

Bryan De-Lara

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
 
If you can compute this on paper, you can compute it in Excel. How would you
compute this on paper?

Tyro
 
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
 
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.
 
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
 
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
 
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
 
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
 
Back
Top