include that weekends are free

    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


    Quote from saban123

    just 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.

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

    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

    Code
    1531/2132	SKUPINA 1	12	21/5/2006	23/5/2006
1321/3212	SKUPINA 1	12	21/5/2006	24/5/2006


    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.

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

    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

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

    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.

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

    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 <> ""

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

    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

Participate now!

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

Register Yourself Login