PDA

View Full Version : networkdays-function



saban
01-14-2006, 04:15 AM
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

Bob Phillips
01-14-2006, 05:41 AM
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.

saban
01-14-2006, 09:54 AM
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

Rembo
01-15-2006, 03:22 PM
Hello saban,

We had somewhat of a similar thread on another forum:

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?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

saban
01-16-2006, 03:02 AM
thnx
I will give it a try and let you know

saban
01-16-2006, 03:11 AM
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??

Rembo
01-16-2006, 06:02 AM
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:



Assignment days needed days till deadline
1 4 5
2 5 9

1) Each of the assignments can be finished before their own deadline because 4<=5 and 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

saban
01-16-2006, 07:03 AM
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)

saban
01-16-2006, 07:04 AM
It would be easier if I had separate sheets for each translator, so I would not bother with rows

saban
01-16-2006, 07:10 AM
for mirza:
nr of pages :21
date:12/01/2006
deadline: 15/01/2006
days needed : 42- that is strange

Why is that???

Rembo
01-16-2006, 07:18 AM
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)

Are you using a localized version of Excel (e.g. a Russion or German version) and if so, which one?

Rembo

saban
01-16-2006, 07:19 AM
can you check this workbook?

Rembo
01-16-2006, 07:22 AM
It would be easier if I had separate sheets for each translator, so I would not bother with rows

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

saban
01-16-2006, 07:22 AM
I think I solved the problem with formula there were "," instead of ";" I think I am using english version (menu is in English language?)

saban
01-16-2006, 07:26 AM
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!!

saban
01-16-2006, 07:27 AM
Maxbe there should be *1/6 and not *6???

Rembo
01-16-2006, 07:32 AM
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

Rembo
01-16-2006, 07:34 AM
I think I solved the problem with formula there were "," instead of ";" I think I am using english version (menu is in English language?)

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

Rembo
01-16-2006, 07:36 AM
Maxbe there should be *1/6 and not *6???

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

Rembo
01-16-2006, 08:11 AM
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



Did you look at that workbook do you think I am thinking right or not?

saban
01-16-2006, 09:06 AM
Why do I get this #NAME error in columns G and H
Any Ideas

saban
01-16-2006, 09:09 AM
I have enabled under tools analysiy pack and still doesnt work should i enable some other option

saban
01-16-2006, 09:15 AM
=(NETTO.WERKDAGEN(D5;E5;0)) it looks like this shouldnt it write WORKDAYS??

saban
01-16-2006, 11:30 AM
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

Rembo
01-16-2006, 03:00 PM
=(NETTO.WERKDAGEN(D5;E5;0)) it looks like this shouldnt it write WORKDAYS??

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.


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

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.

Rembo

saban
01-17-2006, 01:14 AM
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

saban
01-17-2006, 01:41 AM
check this workbook

if it says "zaseden" then transl. is not free
and if it says "prost" transl is free

Rembo
01-19-2006, 03:46 PM
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.


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?

If you use NETWORKDAYS it doesn't count days on the weekend.


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

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:

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

saban
02-04-2006, 04:16 AM
thnx I willl let you know how far did i manage to come
stay cool
saban