Does anyone knows where should i put Networkdays function in my workbook
so that when in column G it checks if "OVER" or not, it exclude weekends
Weekends should not be available!! The translators are free at weekends
thnx
Printable View
Does anyone knows where should i put Networkdays function in my workbook
so that when in column G it checks if "OVER" or not, it exclude weekends
Weekends should not be available!! The translators are free at weekends
thnx
I think we need more information about the date rules. It is not clear from the spreadsheet.Quote:
Originally Posted by saban
In column C there are number of pages of specific document to translate. Translator is able to translate 6 pages per day in column E is the date that document was assigned to translator and in column F is the deadline (the date the document must be translate to)
What I would like to achieve is:
Is translator able to translate document till given deadline, if he can translate 6 pages per day (And on weekends translators are free). If he is able to translate it, I assign to him another document with some pages and the workbook checks if he will be able to translate another document if translator is busy to max in G column will it write over
Hello saban,
We had somewhat of a similar thread on another forum:
http://www.puremis.net/excel/cgi-bin...num=1136318939
Here's an abstract of a reply I made there, perhaps you can use it to solve your problem.
**
This function will calculate the working days between the beginning of the year and the 27th of January and will therefor result in 20.
=NETWORKDAYS(DATE(2006,1,1),DATE(2006,1,27),0)
Note that you can also use references to cells that contain a date value. For example, let's say the date 1/27/2006 you want to use is in cell A1, then you could use this formula:
=NETWORKDAYS(DATE(2006,1,1),A1,0)
Applied to your question if the translater is able to translate a document before a given deadline you could do the following. You know the number of pages to translate, they are in column C. Let's assume your data starts at row 1. The pages that the translater can translate can be calculated as follows:
=NETWORKDAYS(E1,F1,0) *6
If this number is bigger then the number pages the translater has to translate then it can be done:
=IF(NETWORKDAYS(E1,F1,0) *6 >= C1,"Yes, it can be done","No, not possible")
Hope that helps,
Rembo
thnx
I will give it a try and let you know
just one question but what if translator gets one document that he is able to translate, and meanwhile he gets another one, how to check if he is able to translate both of them??
This is actually a mathematically complicated matter. For just two assignments it can be easily solved though.Quote:
Originally Posted by saban
Assuming you will use a row for each assignment, you will have to add up the needed days for each of the assignments. For example, say your translater has an assigment that is listed in row 1 and another in row 3. This formula calculates the total days needed to complete his work.
=(NETWORKDAYS(E1,F1,0)+NETWORKDAYS(E3,F3,0)) *6
To set this of against the days until the deadline, you will need to look at two things.
1) You will have to verify that each of the projects can be done before it's own deadline. This is what is done in the previous message.
2) You will have to verify that the days needed to do both of the projects are less or equal then the largest number of days till the deadline of both projects.
An example:
Say assigment 1 takes 4 days to finish and assignment 2 takes 5 days to finish. The deadlines for each assignment is respectively 5 days from now and 9 days from now. In table view:
1) Each of the assignments can be finished before their own deadline because 4<=5 and 5<=9HTML Code:Assignment days needed days till deadline
1 4 5
2 5 9
2) Days needed for both assignments is 4+5 = 9 days. Max of days till deadline is also 9 days. 9<=9 so the answer is yes, it can be done.
Rembo
Why do I always get an error for the formula written above??
Any ideas(when I copy and paste it to excel I get an error that something is wrong with formula)
It would be easier if I had separate sheets for each translator, so I would not bother with rows
for mirza:
nr of pages :21
date:12/01/2006
deadline: 15/01/2006
days needed : 42- that is strange
Why is that???
Are you using a localized version of Excel (e.g. a Russion or German version) and if so, which one?Quote:
Originally Posted by saban
Rembo
can you check this workbook?
You can refer to cell on other worksheets as well in your formula. For example:Quote:
Originally Posted by saban
=(NETWORKDAYS(E1,F1,0)+NETWORKDAYS(Sheet2!E1,Sheet2!F3,0)) *6
Rembo
I think I solved the problem with formula there were "," instead of ";" I think I am using english version (menu is in English language?)
Aha I can refer to other sheets that is cool, but infact I dont need to refer to other sheets I am calculating(at least trying :)) the availability for each translator so I dont have to do any calculation between translator I guess? Did you look at that workbook do you think I am thinking right or not?
Thnx for all your help
appreciate it!!
Maxbe there should be *1/6 and not *6???
I think I located the problem. To use the NETWORKDAYS function you will need to install the 'Analysis Toolpak'. To install this tool:
1. Click "Tools" - "Add-Ins".
2. Check "Analysis ToolPak".
3. Click "OK".
4. Follow the on-screen prompts or instructions
Hope that solves it,
Rembo
Ah.. ok. You are using the English version then but your regional settings are different. They are the same as mine, I also have to use ; to seperate arguments. With English regional settings this is a comma (,) but not for us. I initially thought you were using an all English setup of Excel.Quote:
Originally Posted by saban
Rembo
No, I don't think so. The NETWORKDAYS returns the number of working days between two dates.Quote:
Originally Posted by saban
If your translater can translate 6 pages per day then you have multiply with 6 to get the total of pages he can translate in that number of days:
5 days * 6 pages/day = 30 pages.
Rembo
Seems a bit complicated to me saban but then again, I'm not sure what you need for your administration. If you look at the attached workbook, I've setup a sample calculation for mirza in Sheet2.
Hope that helps,
Rembo
Quote:
Originally Posted by saban
Why do I get this #NAME error in columns G and H
Any Ideas
I have enabled under tools analysiy pack and still doesnt work should i enable some other option
=(NETTO.WERKDAGEN(D5;E5;0)) it looks like this shouldnt it write WORKDAYS??
Can this be done for 3 or 4 or 5 and so on documents I mean will it calculate right if I assign to him more than 2 documents
Hey, that is the Dutch function name. Yes, it should read NETWORKDAYS in stead of NETTO.WERKDAGEN. Normally function names are translated along with the workbook but it seems that doesn't go for the Analysis Toolpak? Anyway, the solutions is to change the function name to NETWORKDAYS.Quote:
Originally Posted by saban
You mean with more then two documents for a translator? Yes, it can be done but it involves quite some math. Perhaps it would be easier to capture the solving logic in a VBA routine (macro) but it will take some time to figure it out. It's probably easier to simply 'plan' the translation work and see if it all works out as you go.Quote:
Originally Posted by saban
Rembo
For 2 documents it works fine but how would be possible to check for 3 or more documents. The workbook(sheet1) in attachment does that but it does not exclude weekends from calculations? Do you know how could I exclude weekends from sheet1 I must say you did a great job with "mirza" on sheet2 but I really need calculations for more than 2 documents infact I do not know how many documents will he recieve as long as he is able to translate them( could be 10 documents or just 2 depends on what is the capacity)
You said It would be easier done with VBA do you have any ideas what the code should look like
thnx for your time and patience
check this workbook
if it says "zaseden" then transl. is not free
and if it says "prost" transl is free
Hello Saban,
Sorry for my late reply, I've been rather busy. I'm still a little short on time but I'll see if I can help you out here.
If you use NETWORKDAYS it doesn't count days on the weekend.Quote:
Originally Posted by saban
Yes, but then it's easier to solve this by simply starting to plan time for the assignments. For example, let's say your translater has to translate 3 documents:Quote:
Originally Posted by saban
Document 1: takes 3 days, has to be finished by the end of 29 january
Document 2: takes 2 days, has to be finished by the end of 27 january
Document 3: takes 4 days, has to be finished by the end of 2 February
Can this be done?
1st available workingday is 20 January
Total working days needed is 3+2+4 = 9 days
First document to finish is document 2 because that deadline is closest. Plan this one, so that will cost you 2 working days. Needed working days for the rest of the documents is 9-2 = 7 days.
If your tranlator works on this for 2 days then he start on the next document on 24 January.
Second document to finish is document 1. Planning this one:
3 days, starting from 24th = end of 26th. This one can also be done in time.
Working days left = 7-3=4 days. Next available working day is 27 January.
Your translator needs 4 days for document 3 which means the document is finished at the end of 1 February. Also in time.
Conclusion: it can be done.
The pattern here is that you simply start to plan and see if it fits as you go along. Start with the document that has the nearest deadline, then move on to the next etc. That is how you solve this kind of problem.
I don't have time to translate this into VBA right now but if I can spare some time next week I'll help you out (if that is still needed).
Regards,
Rembo
thnx I willl let you know how far did i manage to come
stay cool
saban