find anywhere in A and B anything from C

    well I need that: in column C shouldnt matter in which cell date is written, if the date in column C is between dates in col. A and B it should check that
    ( for example : In cell A3 and in cell B3 dates are written- 21/05/2006 in A3 and 24/05/2006 in cell B3 and in cell C1 date is written -22/05/2006
    Now I need column D to say "true" or "false" if date in C is in that range
    Now if date in C1 is between A3 and B3 should say "true" else "false"
    and so on, It shouldnt matter in which cell in C column date is it should find every date in columns A and B that coresponds to date in C column


    thnx for ideas

    Re: find anywhere in A and B anything from C


    If you enter this formula in cell D1


    =IF(AND(C1>=$A$3,C1<=$B$3),TRUE,FALSE)


    and fill the formula down as many rows in column D as required it should display True or False depending on whether the date falls between A3 & B3.


    I have assumed that by "between" you mean including the dates in A3 and B3. If you want to exclude those two dates (e.g. 21/05/2006 should produce a result of FALSE) the formulas should be


    =IF(AND(C1>$A$3,C1<$B$3),TRUE,FALSE)


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: find anywhere in A and B anything from C


    it works only for each row separately I need that date in C is written maybe in C3 and then it checks this C3 with A1 and B1 and then with A2 and B2 and so on and then when in C2 date si written it is checked with dates from A1:B1 and A2:B2 .... and so on


    Do you know what I mean it is kinda hard to explain

    Re: find anywhere in A and B anything from C


    Perhaps you could upload a sample workbook for us to have a look at. If the workbook shows the data that will exist, and the results that you want to see, we should be able to figure it out from there.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: find anywhere in A and B anything from C


    Hi


    Do you mean that column C will only ever have 1 date in the entire column at any one time? So if there is something in C1, it will be the only entry in column C?



    Tony

    Re: find anywhere in A and B anything from C


    Is this really so hard to check every cell in C column with every cell in A column and B column

    Re: find anywhere in A and B anything from C


    It's not that hard to check every cell in column C, but you don't have any data in columns A and B.


    From what I can see, you want to check the value in AE2 against the start and end dates in columns E & F to find out on which row the date in AE2 falls between the two dates. Having found that, in your example, the date falls into document 2 in row 3, you want to take the value from column AG of that row, but from there on I'm lost.


    Having found the value 12 in AG3, do you then want to deduct the value to the right of the date you are using (AF2 = 2) and then put the result into AF3? If so, why is the value in AF3 at the moment 12, not 6?


    Also, how many values are you going to have in column AE? If there will be more than 1, how will the second and subsequent calculations work?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: find anywhere in A and B anything from C


    Yes you are right and there is a problem how will calculations worked if more than one date in AE is TRUE I mean is between dates


    You see I am looking foe a solution that will:


    1.check if date is between this start and end date
    2.If it is between then deduct 6 from criteria-(how many pages can translator translate / day)
    3.Here is the problem: I would like to just write the days of holidays in a cells in column (AE) and then formulas should check wether criteria is to change or not if date in AE is Between start and end date then Yes change criteria (deduct it by 6) and now if there is another day in AE that fits between start and end date deuct criteria again - and that is the problem I cant find suitable solution for this


    All I want is to write holidays in column and formulas should check where do those dates corespond to start and end dates


    any ideas
    and thnx for all your help

    Re: find anywhere in A and B anything from C


    =SUMPRODUCT(($A$1:$A$5<c1)*($B$1:$B$5>c1))
    where $A$1:$A$5 house FROM dates, $B$1:$B$5 house UNTILL dates, col C houses dates to be checked. Result 0 means the ^date in col is NOT between any pair of dates in cols a and b. Results greater than 0 mean the check dates lies in between that many pairs in a nd b

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

    Re: find anywhere in A and B anything from C


    No man sorry it i snot quite what I am looking for actually I dont know anymore what the best solution for this holidays is?


    Do you have any ideas how can I do that with holidays:
    I wrote something like this:
    When date is between start and end date - first calculate day diference between start and end date, then divide 6/day diference * nr of heads
    and then I deduct this result from criteria(pages/day), so I get new criteria
    But I only manage to do this for one date for each document but I just cant figure out how to do this for multiple days (that translator is on vacation more than one day) :(


    I am lost


    Any Ideas

    Re: find anywhere in A and B anything from C


    in the attached file there is a sheet Absences where you enter who is away from / till
    on the "mainsheet" col A shows whatever date col B counts how many are away on the day specidied in col A. You may use that result for further calculatons. E.G. your pages/per day would usually be 48. so in that col you enter = 48 - (colB * 6) which gives you 42 or 36 depending if 1 or 2 are away.
    If you are not sure how to implement this, then upload your present file so i can try to put his in.

    Re: find anywhere in A and B anything from C


    =SUMPRODUCT((HOLfrom<A7)*(HOLtill>A7)) ??
    what does this mean "HOLfrom", "HOLtill"


    I will send you sample of my workbook and where you deduct 6 from criteria 48 that means that it will deduct criteria for all days that document is being translated , I think I should do something like 6/networkdays(start d.;end d.)*nr of translators on vacation


    Can you pliz show me how would your example be implemented in workbook


    thnx for everything

    Re: find anywhere in A and B anything from C


    It is a cool thing to show me how many translators are absent on that day but I dont need this I guess I need something that will find if date written in absence sheet is date between start and end date (date and deadline) in mainsheet and if it is then calculate new criteria regarding to how many translators are on vacation on that day


    So in main sheet I must find all documents that have date from absence sheet, between date and deadline on main sheet


    Am I thinking right??


    thnx and stay cool

    Re: find anywhere in A and B anything from C


    in the attached file on the mainsheet in col D the workrate for the group is shown. This based on the groupsize and the capacity per head. These two Items are maintained on sheet 2. Both have been named (named ranges". In addition to this the absences (also on sheet 2) are checked. If on the date in col F the size of the goup is reduced because someone is on vacation, then the reduced grpsize (groupsize minus number of bsentees) is multiplied by the per-head-capacity. The from to and till columns have also been named.

    Re: find anywhere in A and B anything from C


    sorry for such a long response I just came from holidays (the short ones)
    thnx for your patience and time I will check this out it looks very good
    thnx for everything and stay cool

    Re: find anywhere in A and B anything from C


    But your example does not work ??
    (Have you just wrote what I should do or should this example work? Cause I get like class not registered or something when I open the file should there be any controls on this sheet(control buttons and so on)-failed to load activeX controls is the error)


    Is this formula correct in D column=(GrpSize-SUMPRODUCT((HOLfrom<=F5)*(HOLtill>=F5)))*CapPerHead
    What are those HOLfrom HOLtill CapPerhead should I write the formula with this records in it like HOLfrom= is on sheet2 in column G and so on.. or what or can I just leave it HOLfrom or HOLtill ??


    thnx

    Re: find anywhere in A and B anything from C


    GRPsize, HOLfrom etc are named ranges. If you want to copy from my file to yours you will have to name the corresponding ranges in your file. E.G. on sheet2 you might have a table with let's say absence. In a2:c37. Instead of referring to sheet2!$a$2:$c$37 all the time, you may select those cells and then give them a name. Inser, name, etc. A named range can be single cell or a range of cells. In the upper left corner of the sheet you may also select named ranges. Check for named ranges in my file to get accustomed to them.

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

    Re: find anywhere in A and B anything from C


    Aha that is cool so I just give a range of cells a name I didnt know that thnx
    will try it and let you know


    thnx for everything and stay cool
    saban

    Re: find anywhere in A and B anything from C


    Why cant I name that ranges from G3 to G5 with name HOLfrom? In capperhead and grpsize it shows names in name box but when it try to set name for HOLfrom or HOLtill it always shows just cell name like G4 or G5.. in name box I guess it should show HOLfrom or HOLtill so the formula on main sheet can recognize those ranges


    any ideas
    thnx

Participate now!

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

Register Yourself Login