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
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.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.
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?
Rembo
can you check this workbook?
You can refer to cell on other worksheets as well in your formula. For example:
=(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.
Rembo
No, I don't think so. The NETWORKDAYS returns the number of working days between two dates.
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
Posting Permissions
Reply With Quote
