# Formula with date

2. ## Re: Formula with date

Originally Posted by FlameRetired
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

3. ## Re: Formula with date

Originally Posted by FlameRetired
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;

Ale

4. ## 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. ## 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.

ok I'm sorry

7. ## 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:

 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

8. ## 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?

9. ## 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. ## Re: Formula with date

Originally Posted by FlameRetired
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

11. ## 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:

12. ## Re: Formula with date

Originally Posted by FlameRetired
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:

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

13. ## 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. ## 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. ## 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.

Page 2 of 2 First 1 2

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

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