Formula not working

[Walker_Ice]

Hi everyone,

This IF AND formula does not seem to be working and i can't figure out why. Can someone please help?

thANK YOU.

=IF(AND([@Type]="income",shift_late_income_status="Active",DAY([@Date]>=shift_late_income_starting_day),
DATE(YEAR([@Date]),MONTH([@Date])+1,1),
[@Date]))

 

[Walker_Ice]

test_tracker_debugger.xlsx
	A	B	C	D	E	F	G
1
2	Date	Type	Category	Amount	Details	Balance	Effective Date
3	1-Jan-25	Income	Side Hustle (Net)	3,500		3,500	=IF(AND([@Type]="income",shift_late_income_status="Active",DAY([@Date]>=shift_late_income_starting_day), DATE(YEAR([@Date]),MONTH([@Date])+1,1), [@Date]))
4	4-Jan-25	Expenses	Groceries	300		3,200
5	8-Jan-25	Savings	Retirement	1,000		940
6	8-Jan-25	Expenses	Fun & Vacation	1,260		940
7	13-Jan-25	Income	Dividends	90		1,030
8
9
10
11
12
13
14
15	Shift Late Income:	Active
16
17	Starting on day x in month:	20
18
Sheet1

Cell Formulas
Range	Formula
F3:F7	F3	=SUMPRODUCT([Amount],--([Date]<=[@Date]), ([Type]<>"income") * (-1) +([Type]="income"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B17	Expression	=shift_late_income_status="inactive"	text	NO
D3:D7	Expression	=B3="income"	text	NO
C3:C7	Expression	=ISNA(MATCH(C3,INDIRECT(B3),0))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A3:A7	Date	>1/1/1900
B3:B7	List	Income, Expenses, Savings
C3:C7	List	=INDIRECT(B3)
D3:D7	Custom	=ISNUMBER(D3)
B15	List	Active, Inactive

 

[Peter_SSs]

formula does not seem to be working

You haven't explained what it is supposed to be doing.

Assuming the two named ranges are the blue cells, my best guess is

=IF(AND([@Type]="income",shift_late_income_status="Active",DAY([@Date])>=shift_late_income_starting_day), DATE(YEAR([@Date]),MONTH([@Date])+1,1), [@Date])

 

[Walker_Ice]

That worked! Thank you for your help.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

BTW, if you are interested, in that formula you could replace
DATE(YEAR([@Date]),MONTH([@Date])+1,1)
with the simpler
EOMONTH([@Date],0)+1

So the formula would become

=IF(AND([@Type]="income",shift_late_income_status="Active",DAY([@Date])>=shift_late_income_starting_day),EOMONTH([@Date],0)+1,[@Date])