Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 04-22-2019, 05:59 AM
aleman_li aleman_li is offline Formula with date which need to match & got error message Windows 10 Formula with date which need to match & got error message Office 2016
Novice
Formula with date which need to match & got error message
 
Join Date: Apr 2019
Posts: 2
aleman_li is on a distinguished road
Default Formula with date which need to match & got error message

New beginner.
Version Window 10
Excel 2016 but my colleague Version 2010
I had French version using ( to seperate the formula but my colleague has English version which using (,)
Actual database with 18000 lines on Base file & 200 lines on BAF table.


I have two table which has different range of date, BAF value & the helper column which can find in following table .
The principal & the goal is to match the range of BAF table & give the correct period of information & if it's out of range, got error message.
It can allow duplicate record & can add the data on both BAF & Base file with new information.


Example
Case 1 : Base table : dated 1/3 to 31/3, BAF table has information : 1/3 to 31/3, 1/4 to 30/4, 1/5 to 31/5.
I would like the data only take the information from 1/3 to 31/3. (not 1/4 to 30/4 & 1/5 to 31/5)


Case 2 : Base table : Dated 1/4 to 15/4 & 16/4 to 30/4.
BAF table (same as case 1) : the result need to show between 1/4 to 30/4.


Case 3 : Base table: (a) dated 1/3 to 31/3 or 1/7 to 31/7 which assume BAF table does not have those period which need to show error message : N/A.
(b) dated 1/3 to 1/6 but BAF table has only the rate from 1/3 to 31/5 only, error message : end date larger than validity. pls input correctly.


Case 4 : Base table : dated 1/4 to 31/5, BAF table, same as Case 1.
I would like the result only show Apr BAF table.
Case 5 : Base table: dated 1/4 to 30/4, BAF table, 1/4 to 30/6.
I would like the result can show the BAF value without problem.




Base file table (around 18000 lines)
Start dateEnd dateHelper 1BAF rate1/430/4ABCDE (it will be change accordingly to the combination)2001/531/5ABCDE2501/331/3ABCDE130

BAF file table (around 200 lines)
Start dateEnd dateHelper 1BAF value1/331/3ABCDE130

If you need any further question for set up the formula, I also att'd the file for easy understand.

Tks for your help in advance !


Rgds
Ale
Attached Files
File Type: xlsx formula with date need to match.xlsx (13.3 KB, 12 views)
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Word Error Message Run time Error 4605 baes10 Word VBA 1 08-30-2018 02:37 PM
Formula with date which need to match & got error message Reserved error(-1517); there is no message for this error ktest Office 1 02-12-2016 08:08 PM
Formula with date which need to match & got error message Automation error Unknown error" message once they open the Excel file hlina Excel 1 10-08-2013 09:14 PM
Macro date and match valor cg1980 Excel Programming 0 04-12-2011 09:38 AM
Formula with date which need to match & got error message Imported message date change to today's date promark Outlook 1 12-23-2005 07:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft