Range help

Hi, is there any way to compare to files using a date range?

like: i have 2 file having jan and feb data.

I want to compare previous month with current: i.e Jan with feb and want to identify all new entries and then compare these entries with a range.

example: comparing jan with feb we will see new entries in feb month. then we should compare this start date range (02/01-02/29) and for jan (01/01-01/31 and if not in range it will show as "DISCREPANCY/ALERT"

i might approach by joining the two files and see the remaining data in r anchor or j anchor and then putting the criteria. not sure though.

Feb.xlsx

January.xlsx

Error Message

Output

Preparation

Accepted answers

binu_acs

@Navya08 One way of doing this. Make sure the month is present in the input file name

descripency_aletrt_24042024.yxzp

binu_acs

@Navya08 you can write all in one single formula as well, I created two seperate columns to avoid complexity and it is easy to understand, also to make it dynamic I used the file name as the month and from that I derived the first of the month and last of the month date for the formula.

For your formula you are checking the date range between first of Jan and end of Jan, I think you need to inter change the end result like below, if it is falling in the range then no descripency else descripency

IIF([start date]>="2024-01-01" and [start date]<= "2024-01-31","No Discrepnacy","discrepancy")

All comments

binu_acs

@Navya08 One way of doing this. Make sure the month is present in the input file name

descripency_aletrt_24042024.yxzp

Navya08

@binu_acs Just one more thing, if i have timestamp IN my date field- how can i remove that- i am trying using formula tool- Datetimeformat ([startdate], "month"), %Y-%m-%d")

Its not showing correct output.

Eg:2024-01-10 00:00:00

binu_acs

@Navya08 use the function toDate() and change the data type to Date

toDate([startdate])

Navya08

@binu_acs you created diff columns start date and end date

what if i use single formula tool and write IIFstatement [start date]>="2024-01-01" and [start date]<= "2024-01-31","Discrepnacy","no discrepancy")

why is the output wrong?

binu_acs

IIF([start date]>="2024-01-01" and [start date]<= "2024-01-31","No Discrepnacy","discrepancy")

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703