LinkBack URL
About LinkBacks
Hi, i am filtering both Start and End with a day.
However, my formula only work without error IF both filter return a valid result.
How do i solve this?
Please see my file
Hi, i am filtering both Start and End with a day.
However, my formula only work without error IF both filter return a valid result.
How do i solve this?
Please see my file
Please try:
=LET( start,FILTER(Table1,EOMONTH(--CHOOSECOLS(Table1,2),0)=$E$1), end,(FILTER(Table1,EOMONTH(--CHOOSECOLS(Table1,3),0)=$E$1)), t,VSTACK(start,end), IF(t="","",t))
Hi, thanks for your help. But my actual file has some calculation in using the date. It still shows error.
Give me a minute. I update my excel. Sorry.
Last edited by AliGW; 01-08-2024 at 03:35 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
Hi, HansDouwe, just discover that you misunderstood my meaning. My file, if i change G1 to other day like 30/07/2023, i will get a #CALC! error, i believe its due to becoz one of the filter return nil.
Hence i am trying to make it show only valid result.
Last edited by AliGW; 01-08-2024 at 03:39 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
Try:
=LET(
start,FILTER(Table1,EOMONTH(--CHOOSECOLS(Table1,2),0)=$G$1),
end,(FILTER(Table1,EOMONTH(--CHOOSECOLS(Table1,3),0)=$G$1)),
IFERROR(VSTACK(start,end),""))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi, the formula do not work if i type 31/05/2023 and 31/10/2023. If i type in 31/05/2023, it should show only the Start and End should be nil.
However, i get no result. Kindly help.
Last edited by AliGW; 01-08-2024 at 12:58 PM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
Post a file showing what you ecxpect to see for 31/5/23. I must have misunderstood you, as there are NO values that START in may 23 and which also END in May 23... which was my understanding of your requirement.
Hi, please see attached. Thanks for your help.
Really appreciate.
I asked for expected answers, not a non-working formula. So another (final) guess.
If this is still incorrect, I need a proper explanation and expected results.
Please try this formula:Formula:
=LET(f,FILTER(Table1,Table1[Start]<=G1,Table1[End]>=G1),IF(f="","",f))
Modifying Hans' formula in post #2,
PHP Code:=IFERROR(LET(
start,FILTER(Table1,EOMONTH(--CHOOSECOLS(Table1,2),0)=EOMONTH($G$1,0)),
end,IFERROR(FILTER(Table1,EOMONTH(--CHOOSECOLS(Table1,3),0)=EOMONTH($G$1,0)),""),
t,VSTACK(start,end),
IF(t="","",t)),"")
Hi to all, thanks everyone there for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks