Excel Calculation problem

[Yogin]

Hi,

i have set up a spreasheet which gives me total headcount available by
regime and then convert each regime in hours.

EG. NL = Nights Loading staff, N = Nights Pick Staff, NG = Nights Goods in
Staff, NHOL = Holiday and so on..

I then carry out a countif sum to give me total available by day for each
regime. And on another worksheet I convert this into hours. I give each
regime a value.

EG. NL = 8hrs Loading, N = 8 Hrs Picking and so on.

If everyone worked an 8 hour shift this works fine but on occasions people
book 2 or 4 hours holiday or leave work early, but problem i am having is
that when I input the code NHOL4, this takes full 8 hours off from for
example NL (total hours) and not 4 as this person has worked and put the
other 4 hours into Holiday.

Can someone help me with this as I have done everything to correct this and
cannot find a solution.

Thanks

Yogin

 

[John C]

Can you give more specific examples? You talk about code of NHOL, but then
refer to NHOL4 later on. If someone isn't using all 8 hours for a specific
code, are you then tacking on how many hours that code is using?

 

[Yogin]

Hi John,

Thank you for replying back so soon.

Eg. I have someone who is planned planned to work on nights Loading (NL)
this equeates to 8 hours, but during the shift I have authroised 4 hours
holiday, so what I do is I change NL to NHOL$ (4 hours). In my countif some
this takes away full 8 hours from Nights Loading and then transferring 4
hours into NHOL4. What should happening is instead of taking the full 8
hours, in the NL regime I was looking for a formula to take into
consideration that this person has worked 4 hours and the remaining 4 hours
are as holiday. So NL = should equal to 4 hours instead of 0.

I hope this make sense if not I can email you an example of the spreadsheet
i'm working on.

Thanks

Yogin

 

[John C]

I still would like to see an example layout of some data. If you change the
NL to NHOL4, how do you know that the other 4 hours would be NL, and not NG?
Are there multiple codes possibly entered for a person? i.e.: NL, NHOL4.

Further clarification, if someone has 4 hours of Loading, and 4 hours of
holiday, what code do you enter. If you enter just NHOL4, how would excel
know that the other 4 hours are NL, and not NG.

 

[Yogin]

Hi John,

That is what I'm trying finally come to so that when I change NL or NG to a
diffrently regime which is less then 8 hours to calculate the right amount in
to the right fields.

Do you have any ideas if this is possible.

Yogin

 

[John C]

Ok, let me rephrase my question. You have a data tab that presumably has an
employee name, then a specific date, then a code field, and possibly some
other stuff too. Do you only have 1 field capable of the code field?

John Alpha 24/07/08 NL
Betty Bravo 24/07/08 NG
Clifford Charlie 24/07/08 NHOL4 .... but with no accounting that is
usually NL

or do you have multiple fields capable to enter codes. Without knowing your
data structure, or what kind of datastructure you are wanting, I really can't
go forward.

 

[Yogin]

Hi John,

Aplogies for that,

My Data is set as below

25/07 26/07 28/07
Name
Person1 NL NL NL
Person2 NL NL NHOL4
Person3 NG NL NL

Ttl NL 2 3 2
Ttl NG 1 0 0
Ttl N 0 0 0
Ttl NHOL4 0 0 0

I have each cell set up as Validation list (this allows me to change the
code from NL to NG etc.

On another worksheet, I have set up a table giving values to each code and
the multiplying this by the total count

25/07 26/07 27/08
NL 8 16 24 16
NG 8 8 0 0
N 8 0 0 0
NHol4 4 0 0 4

I have one field and then change the code using the validation list by
person by day.

Hope this makes sense.

Thanks

Yogin

 

[John C]

On your current setup, no, there really isn't a whole lot you can do. What
you need is more codes, and I am not sure you want that route. For example:
Instead of NHOL4, you could break this into 3 separate codes: NLHOL4, NGHOL4,
NHOL4.

Then, to total, for example, NL, would be as follows:
=COUNTIF(B$2:B$50,"NL")+0.5*COUNTIF(B$2:B$50,"NLHOL4")
This would count 1 for every NL and 1/2 for every NLHOL4. So if you had a
code that would be NLHOL4, the 1/2 shift (4) would be captured in the second
part of the equation.
In addition, your total that is currently labeled NHOL4, you could just
label as HOL4, and your formula for tabulation would be as follows:
=COUNTIF(B$2:B$50,"*HOL*")

Of course, if these are all the codes you have, that's fine, it's really
only 2 additional codes, and your DV list box won't be overworked. If,
however, you have multiple codes such as for personal time 4 hours, 8 hours,
sick time 4 hours, 8 hours, etc. Then your DV list box could be
overencumbered very quickly, and I would maybe recommend a different data
setup, possibly having 2 list boxes per person per day instead of 1, or even
more than that, depending on how compact you need it.

 

[Yogin]

Thanks John,

I don't think additional DV list is an option as I have over 400 colleagues
and 6 different shifts.

I will see if there is alternative using vb code if not manually adjust the
diffrence.

Thank you for your help and I will try your suggest see if it is feasible.

 

[John C]

I understand. It would be different if each person did only 1 type of work,
but, in your example, if person3's third day was a half day holiday, you
won't know if they were NG or NL (or even N) for the other 4 hours. No VB
will help you either, as far as I can tell, because excel cannot 'guess' what
it should be. You will either need more codes, or a different data layout.

Sorry couldn't help you.