+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 30 of 30

Formula with date

  1. #16
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    14,685

    Re: Formula with date

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  2. #17
    Registered User
    Join Date
    09-29-2014
    Location
    Lille, France
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Formula with date

    Quote Originally Posted by FlameRetired View Post
    You are welcome. Glad to help.
    Dear FlameRetired

    After I entre the whole data & found out there is some problem.

    In the base file, If I put the date from 1/3 to 31/3 & 1/4 to 31/5.
    In the BAF rate i had two rate from 1/3 to 31/3 & 1/4 to 30/6,
    the rate from 1/3 to 31/3 which has no problem to show, but the rate from 1/4 to 31/5 which they show error message.

    On the other hand, I had the second case that I delete the third critiea (F2<='BAF file'!$F$2:$F$8) & put the date only from 1/3 to 31/3.
    Afterward, I add the date from 1/4 to 31/5 & the value will show only 1/3 to 31/3. It can show the BAF rate from 1/4.

    Can you help one more time how to amend the formula that it can show two period in the table ?

    I att'd the file again for your reference.

    Tks & Rgds
    Ale
    Attached Files Attached Files
    Last edited by aleman_li; 04-15-2019 at 07:49 AM. Reason: att'd the file for example

  3. #18
    Registered User
    Join Date
    09-29-2014
    Location
    Lille, France
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Formula with date

    Quote Originally Posted by FlameRetired View Post
    You are welcome. Glad to help.
    Dear FlameRetired

    I have problem in formula again, pls find att'd file which has error message.
    Can you assist to solve it ?

    Basically it's because there are two period of date from BAF table & I will put the date range from Base file. It need to extract the data correctly in the Base file;

    Tks again in advance
    Ale
    Attached Files Attached Files

  4. #19
    Registered User
    Join Date
    04-15-2019
    Location
    usa
    MS-Off Ver
    2013
    Posts
    8

    Re: Formula with date

    If I understand correctly, then

    =INDEX('BAF file'!$G$2:$G$5,MATCH('Base File'!E2,'BAF file'!$E$2:$E$5,1))

    in G1 and copy down.

  5. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    24,763

    Re: Formula with date

    Why did you copy another member's post? Your behaviour here is suspicious - be aware that I have my eye on you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #21
    Registered User
    Join Date
    04-15-2019
    Location
    usa
    MS-Off Ver
    2013
    Posts
    8

    Re: Formula with date

    ok I'm sorry

  7. #22
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,878

    Re: Formula with date

    aleman_li,

    Please try array entering this formula in G2 and fill down.

    If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    E
    F
    G
    H
    1
    From
    To
    BAF
    helper 1
    2
    4/1/2019
    4/15/2019
    200
    FEMEDCMADry
    3
    4/16/2019
    4/30/2019
    200
    FEMEDCMADry
    4
    5/1/2019
    5/31/2019
    250
    FEMEDCMADry
    5
    3/1/2019
    3/31/2019
    150
    FEMEDCMADry
    6
    5/1/2019
    5/31/2019
    #NUM!
    FEMEDCMADry
    7
    6/1/2019
    6/30/2019
    275
    FEMEDCMADry
    8
    6/1/2019
    6/15/2019
    275
    FEMEDCMADry
    9
    6/1/2019
    6/30/2019
    300
    FEMEDMSCDRY
    10
    5/1/2019
    5/23/2019
    230
    FENCPMSCDRY
    11
    4/1/2019
    4/30/2019
    200
    NCPFEABCDRY
    12
    5/1/2019
    5/23/2019
    200
    NCPFEABCDRY
    13
    5/24/2019
    5/31/2019
    210
    NCPFEABCDRY
    14
    6/1/2019
    6/30/2019
    210
    NCPFEABCDRY
    Dave

  8. #23
    Registered User
    Join Date
    09-29-2014
    Location
    Lille, France
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Formula with date

    Dear Dave

    Tks for your reply but it seems that the formula does not work.

    I att'd the file that after I input your forumla on it & the data is not the same as your print screen.
    Row 6 has error message with #NUM !" on it & other mistakes on the file.

    Fyi, I am using version 2016 which I don't know if it's the reason that the result is not the same.

    At the same time, when I type the formula which the sign (,) whcih need to type ( in order to pass the formula. It might be the version Windows is in French ?? As when I pass it to my colleague & I noticed that his version 2010 is using (,) too.
    But he send me back the file which the formula change back to ( automatically.

    =INDEX('BAF file'!$G$2:$G$9;SMALL(IF((H2='BAF file'!$H$2:$H$9)*((E2>='BAF file'!$E$2:$E$9)*(E2<='BAF file'!$F$2:$F$9)+(F2>='BAF file'!$E$2:$E$9)*(F2<='BAF file'!$F$2:$F$9));ROW($H$2:$H$9)-MIN(ROW($H$2:$H$9))+1);COUNTIFS(E$2:E2;E2;F$2:F2;F2;H$2:H2;H2)))

    When you use H2 which does not need to refer to BASE file but when I do the formula, it appears the long file name.
    Will it be any problem that I just use H2 like yours ?

    One last question, as the BAF file which actually is not only 9 rows.
    Can i use the column number to put into the formula so that it can cover all the data in that column?

    Tks your for your patience & really tks for your help in advance !
    Attached Files Attached Files

  9. #24
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,878

    Re: Formula with date

    I am having difficulty understanding.

    Please upload a file with all desired results hand typed in. Perhaps I can figure out that way.

  10. #25
    Registered User
    Join Date
    09-29-2014
    Location
    Lille, France
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Formula with date

    Quote Originally Posted by FlameRetired View Post
    I am having difficulty understanding.

    Please upload a file with all desired results hand typed in. Perhaps I can figure out that way.

    Dear Dave,

    Encl pls find those file & my explaination on it.
    Hope it's clear !

    Tks & Rgds
    Ale
    Attached Files Attached Files

  11. #26
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,878

    Re: Formula with date

    Thank you. That helped.

    Please check cell G9. I get 300 which I believe is correct.

    The enclosed will take care of the regional settings "," vs ";".

    The formula must be array entered --- Ctrl + Shift + Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #27
    Registered User
    Join Date
    09-29-2014
    Location
    Lille, France
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Formula with date

    Quote Originally Posted by FlameRetired View Post
    Thank you. That helped.

    Please check cell G9. I get 300 which I believe is correct.

    The enclosed will take care of the regional settings "," vs ";".

    The formula must be array entered --- Ctrl + Shift + Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Dear Dave

    Tks for your renew formula but it's still have few problems as below:-


    1. I noticed that the row/line number in the formula need to match, example 9 rows in BAF table & BASE file table. However, in my real file, the rows number in BAF table which is 173 but my Base file row number which is nearly 18000.
    So I am not sure if it's this problem to cause some error.
    Also, when I add the line/row in BAF table, it seems that it need to include the new row/line number to extract the information. Is there any solution to avoid it ? (eg BAF table add one line for 10 & 11 which the result are not the same in Base file)

    2. When the data is not yet input it in BAF table (eg rate on July), it seems that they take the first record from the file which it's happen in my real file. Can it show error message N/A so that I can input the data.

    3. When the date on Base file which is 1/4 to 30/6 but the BAF file record has data from 1/4 to 30/4 but it show error message. I suppose it should show the Apr data on it.

    I encl the file & show in Orange colour & in RED on the cell itself with some explanation.

    Tks again for your effort !
    Rgds
    Ale
    Attached Files Attached Files

  13. #28
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    9,878

    Re: Formula with date

    1. Since the range in 'BAF file has increased the ranges in the formula need to increase to include the new rows.
    2. However, in my real file, the rows number in BAF table which is 173 but my Base file row number which is nearly 18000.
      You hadn't mentioned that before. Array formulas are resource hungry. Too many of them will cause slow calculations if Excel can handle them at all.

    With all the added information and the complexities of returning overlapping dates (sometimes in the same 'group' (column H) and sometimes not) I am afraid I have exhausted all my ideas.

    I am sorry. I don't think I can be of further assistance.

  14. #29
    Registered User
    Join Date
    09-29-2014
    Location
    Lille, France
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Formula with date

    Dear Dave,

    Yesterday, i retried your formula & actually even the real file has 18000 line which they had no problem.

    I am just thinking the principal how to implement this formula & clear my idea. Might be you can still find the solution on it.

    Basically, in Base file, I just need to clarify the start date (no need to concern the end date) which is between the date from BAF table (from / to) or not.
    If it's in that range, then search for data which match with helper information.
    if not, then it's N/A
    The formula should allow add the new row information on both Base file & BAF file which will not affect the data.

    The problem should be if the data on BAF table has two months information (eg Mar & Apr) which the formula need to find which data should be in Base file accordingly.

    Anyway, Tks so much for your help & I hope that one day I can find the solution on it !

    B. Rgds
    Ale

  15. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    24,763

    Re: Formula with date

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1