Re: include that weekends are free
should I write holidays then with range like from 02/02/2006 to 03/02/2006
or do i just put the number of days
thnx
saban
Re: include that weekends are free
should I write holidays then with range like from 02/02/2006 to 03/02/2006
or do i just put the number of days
thnx
saban
Re: include that weekends are free
Try modifying this code that a member of this forum was kind enough to provide me. My code only counts the time diffence between 9:30am to 6:00pm monday to friday, it shouldn't be too difficult to modify the code to suit your needs. I hope this helps in some way.
Gary Hunt
Re: include that weekends are free
Sorry about my last entry, I submitted the wrong one. Try this one instead.
Re: include that weekends are free
thnx
I will try it
Re: include that weekends are free
Quote from saban123just one more q
what should formula be in J column
=WORKDAY(M3;INT(I3-M3);R3:S3)+(I3-M3-INT(I3-M3))
I guess this R3 and S3 are not needed
r3:s3 must be replaced by the range where holidays are stored. Must be a single collumn list of ALL dates where no work is done. Be it public holidays or transalators vacation. Make sure to specifiy each day seperately, i.e. 5 dates for a full work week (monday through friday). This holiday list may even be on a seperate sheet.
Re: include that weekends are free
thnx
I will try this
Re: include that weekends are free
C = nr of pages
E = Date
F = Deadline
G = IF(Q2>M2;"ocupied";"free")
H = E2
I = IF(H2="";"";F2-(1/AD2*C2))
J = WORKDAY(M2;INT(I2-M2);$AE$2:$AE$100)+(I2-M2-INT(I2-M2))
K = IF(H2="";"";H2+(1/AD2*C2))
L = WORKDAY(H2;INT(K2-H2);$AE$2:$AE$100)+(K2-H2-INT(K2-H2))
M = F2+1
N = IF(O2>0;1;0)
O = IF(B2=B1;MAX(H2;Q1);H2)
P = IF(O2="";"";O2+(1/AD2*C2))
Q = WORKDAY(O2;INT(P2-O2);$AE$2:$AE$100)+(P2-O2-INT(P2-O2))
Could you explain why do I get wrong answer in cell "G" when I put
into cells 12 = nr of pages
21/5/2006 = Date
23/5/2006 = Deadline
appreciate any help
thnx
Re: include that weekends are free
you need to upload your file. maybe stripped down to stay within allowed upload size.
Re: include that weekends are free
Could you check what the problem is
For both documents criteria is 12 pages/day
But it gives me an answer free but it should be ocupied brrr I dont quite get this calculations of your formulas could you see what seems to be wrong
and one more q:
=IF(H2="";"";H2+(1/AD2*C2))
=IF(H2="";"";H2-(1/AD2*C2))
This formula gives me ??
(first it checks H2 which is dateassigned then if condition true ([COLOR="DarkRed"]""[/COLOR])-what does this mean?? and if condition false then H2+(1/AD2*C2)) or H2-(1/AD2*C2))
I dont exactly know how to use this result I am lost:(
Re: include that weekends are free
I ammended the attached to show the correct result in column G. Some columns were referring to the wrong line. Also, the "zaseden" will only appear in this example if the name in column B is the same for both jobs. Else it is assumed that a second person is working. See yello mark.
whatever is beyond column U I could not check for correctness as I do not recall what it was meant to do.
With the formula if xx="" etc I had started avoiding error results in lines with no job yet. This would have to be extended to other formulas as well. the rest of the formula is breaking down the number of pages to be translated into days and/or fractions of days by dividing the pages by the rate-of-translation in col ad
Re: include that weekends are free
Why is when I change the dates of assignement to 21/05/2006 it says "free"
but 21/05/2006 is Sunday ?? It should know that it is sunday any ideas why is that
Oh and thnx for everything you rule!!
Re: include that weekends are free
But i Think that workday - function is only refering to deadline not to date of assignement or what is that right or not
Because if it is OK that way i dont mind we never assigned translators with work on sundays and saturdays
Thnx again
Re: include that weekends are free
I have tested it a little and it works well I guess(you are a genius) but which lines were refering to wrong columns(just curious you dont have to write if you dont feel like to)
I just have question how could I get the information: how many pages can translator still translate if he already has those documents assigned.
(just for information when we are distributing work to see how many pages can one still get)
Any ideas
thnx
Re: include that weekends are free
8 SKUPINA 1 [COLOR="Red"]12[/COLOR] CULT 24/05/2006 26/5/2006 [COLOR="Red"]notFree[/COLOR] 1 [COLOR="Red"]4[/COLOR]
when i put those data in: nr.of pages = 12 and criteria = 4 it gives me not free
but if I put 11 pages it give me free (but from 24/05 till 26/05 should be 3 days and criteria is 4 pages/day so this is 12 pages?? wierd)
Re: include that weekends are free
=NETWORKDAYS(TODAY();F2)*AD2-SUM(C$2:C2)
will give you the capacitiy from today till the deadline in column F.
=IF(O2="";"";O2+(1/AD2*C2)-0.0001)
in column P will correct the problem mentioned in connection with 11 or 12 pages from 24 to 26 may. The reason is, that 24:00 makes the date chenge to the next day. If that next day is a holuday/weekend then it switches to monday. Now the formula deducts one thousadth of a day which gives 23:59 and the day does not switch.
yes, the start date must not be on a weekend or defined holliday. Assigning jobs on those days does not make sense anyway since your thread here is called ".....weekends are free".
Cheers
Re: include that weekends are free
thnx I really appreciate this
And you say I must insert formula =NETWORKDAYS(TODAY();F2)*AD2-SUM(C$2:C2) into empty cell and it will give me the capacity remaining(how many pages can translator get from today till last deadline) ?
Re: include that weekends are free
yes, it should give you the capacity untill the deadline of the respective line.
Re: include that weekends are free
Just one question
=IF(AE2="";AG2;AG2-6) how could I insert another condition that =IF(AI2="";AG2;AG2-3) and first it checks first formula and then the other and then it puts result in desired cell (how could I combine those two conditions in one cell)
thanks
Re: include that weekends are free
Please be more specific about the second condition. the first formula results either inAG" or in AG-6. with a second condition one of these possible results would have to be replaced by your second formula, so to speak. But which one. To give you a correct answer i need to know what result do expect if
AE = "" AI = ""
AE <> "" AI = ""
AE <> "" AI <> ""
AE = "" AI <> ""
Re: include that weekends are free
sorry to bother
How could be done with that capacity remaining ; that it would consider all the documents that are assigned (So when I assigned 2 or 3 .. documents to translator that the remaining pages would be shown according to last deadline)
I mean how can I know how many pages can translator still get until last deadline (if all other documents until last deadline are considered)
Thnx