# include that weekends are free

• Re: include that weekends are free

I guess it shows OK the problem is with diferent criteria if criteria stays the same then it is ok but if criteria is 6 for one document and 4 for other document, then the remaining capacity is not correct

• Re: include that weekends are free

the formula above allready does that. It calculates the total capacity from today till the deadline and deducts the total number of pages in Column 2 from line 2 to actual line. Check what happens if you ammend the page nbrs in C

the rate of the actual line is used, so the result will not be correct if variable rates are used

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

So I guess it works corect only if criteria is the same for all documents right?

• Re: include that weekends are free

yes, that's correct. Both in this file as in real life. You can only determin how much more could be assigned if you know what the work rate is. If you have variable workrates you cannot determin any remaining capacity for sure. Whatever rate you aplly in such a case , it will allways be guessing.
Cheers.

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

thnx for all your help will let you know if I need some more
Stay cool

Thnx

• Re: include that weekends are free

Do you have any ideas why I get result like that

I will attach you sample of my workbook

## Files

• Re: include that weekends are free

Can ypi please check this out because it is the main reason I guess that this is not working 100% you will se in example in ozgrid 1.xls are exactly the sam e records as in ozgrid 2.xls except nr of pages are mixed instead of 3 is 6 and instead of 6 is 3 in second and third row

ozgrid 2.xls has the right result but I guess also ozgrid 1.xls should have the same result but it doesnt.

Do you have any idea why is like that

## Files

• Re: include that weekends are free

it appears that the workday function is not working the way I had expected. Right now I do not have a solution to the problem. However, I shall try to find a solution tomorrow.
As long as you are not useing range AE for holiday information you may change the formula in column G to refer to col P instead of Q.
I'll get back to you as soon as i have a solution.

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

thank you very much I really apreciate this

(I will try to find some solution but I doubt I will figure something out)
So hope you'll figured out something I think I am not yet capable of solving problems like that

stay cool and thnx again
Saban

• Re: include that weekends are free

Actually I a not using AE for holiday information. I use AE for dates which are the holidays but I dont use network days I just decrease criteria for 6 pages if date in AE is between dates in E an F columns (between Date and Deadline)
(I am decreasing criteria if date in AE is between dates from E and F)
But that is not the best solution because then it decreases criteria even on days when translator is not on holiday(I guess I should increase the number of pages for 6 if translator is on holiday one day and if he is on holiday for 2 days then I should increase nr of pages for 12 .. or something like that)-what do you mean

Does this helps you somehow

• Re: include that weekends are free

If I refer to column P instead of Q It will not consider weekends free right?

• Re: include that weekends are free

Hi,
in the attached file i have taken a new and hopefully better approach. It is based on "opening hours". This means your translaters are looked at as you would look at e.g. a repair shop. with opening time and closing time. the difference is the available time to work. On the other side a job is "converted" into a length of time. Together with the workday function it is then added to the starttime of the job, considering weekends and holidays.

Please note the following: the "datum" you specify is "converted" to the earliest start as "datum" plus "opening time". "rok" likewise with closing time. Thus a job may have one and the same "datum" and "rok" and still be "ok" if the number of pages do not exceed the translators daily capacity.

I have added comments to the headers for better understanding of what is happening in the various columns. The opening hours and holiday info are on a seperate sheet. If you want to move them then make sure you drag them with the mouse in order not to break the links.

Check if this serves you better. If yes we can allways add additional features such as remaining capacity etc.

## Files

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

Looks good I will check more detail. Just one thing the date of deadline( lets say 23/05/2006 is the deadline)- this day should not be included as available day for translating

And does on sheet2 in "D2" working time affect anyting because workday is 7,5 hours long or let say 8 hours

And you see it is like this:
This are supposed to be 4 groups of translators and in each group there are 8 translators so this give me 6*8=48-pages to translate per day
And if one of the translators goes for a vacation the group can translate only 48-6=42 pages per day, what do you think how could I approach to this problem?? Any ideas

Thnx for such a quick response
saban

• Re: include that weekends are free

to solve the deadline question, change the formulae in col "latest end" by addinf " -1" (minus one)to it. Then the latest a job must be finished is closing time the day before the deadline.

In your file the opening times do not have an immediate effect, since the the job hours are calculated based on the rate of pages per day. Thus changing the opening hours will also change the jobhours. To check this you may add a job with e.g. 6 pages and a rate of 6. now the job hours will allways be exactly the same as the opening hours since 6 for six means one day in this case.

I will get back with hopefully an answer to the rest.

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

hi Saban,
the changing capacity of a group cannot be fully taken into account. It would be possible to check the situation on "acual start" and also on actual end, however any changes to the capacity that happen inbetween could not be considered. Thus results would be "approximate" i.e. missleading.
Think of a big job of maybe 1000 pages. You start with 8 people, then one goes on vacation, comes back while the job is not done yet. Or even e second one goes on leave. To account for this, I think excel is the wrong tool. At least with formulae only. Extensive programming would be needed, for which I do not have the capacity. Neither in time nor in knowledge.

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

but let say I only change the criteria for one document and instead of criteria 6 I put 4 would i get the right results ??

And how can I be done that the on the day of deadline document should already be translated because the day on deadline is reserved for revision
so the deadline should not be considered as day for translating this document
Any ideas

Thnx for everything

• Re: include that weekends are free

if you change the translation rate per document you should get the right results.
see my post before last for the deadline question. just deducht 1 from the "latest end"

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

Do you think I did it right if I deduct 0,9999 from latest end because in O column you put +0,0001 at the end?

And I get satisfying result if I deduct 0,9999 from latest end

And regarding this changing work rate cna it be done this way:

lets say If date in column P is date that exists between F and G (Datum and Rok) and in column Q I will put how many translators are on date in column P absent. So lets say I have

22/05/2006 and 25/05/2006

and now in column P I have date 23/05/2006 So I check if this date is between dates 22/05/2006 and 25/05/2006 and in column Q I will wrote 1 which means that only 1 translator is absent on 23/05/2006 and therefore I must decrease work rate with 6

Do you think that could do well

Thnx

• Re: include that weekends are free

you may as well deduct 1 since 1 stands for a full day.

for the varying rate (based on number of heads) I suggest you add a list to sheet2 with one column with dates and the next col with number of heads, e.g.
01.06.06 8
03.06.06 7
12.06.06 8
etc.
the above means that in week 23 one translator is off duty.
now in sheet1 you can use vlookup(start-date;absence range;2) where start-date is the the date after the previous job, absence-range is where you have above list, 2 is the second column of that range. This would give you the number of heads at the beginning of this job. The absence-list must be in ascending order for vlookup to work as wanted.

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: include that weekends are free

Sound complicated but I will try

but If I will suceed that is another question
and how could I get information about remainig pages until last deadline

Thnx for all your help I would crap my pants without you

stay cool

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!