Formula not working

Walker_Ice

Board Regular
Joined
Oct 6, 2023
Messages
53
Office Version
  1. 2021
Platform
  1. MacOS
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]))
 
test_tracker_debugger.xlsx
ABCDEFG
1
2DateTypeCategoryAmountDetailsBalanceEffective Date
31-Jan-25IncomeSide Hustle (Net)3,5003,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]))
44-Jan-25ExpensesGroceries3003,200
58-Jan-25SavingsRetirement1,000940
68-Jan-25ExpensesFun & Vacation1,260940
713-Jan-25IncomeDividends901,030
8
9
10
11
12
13
14
15Shift Late Income: Active
16
17Starting on day x in month:20
18
Sheet1
Cell Formulas
RangeFormula
F3:F7F3=SUMPRODUCT([Amount],--([Date]<=[@Date]), ([Type]<>"income") * (-1) +([Type]="income"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B17Expression=shift_late_income_status="inactive"textNO
D3:D7Expression=B3="income"textNO
C3:C7Expression=ISNA(MATCH(C3,INDIRECT(B3),0))textNO
Cells with Data Validation
CellAllowCriteria
A3:A7Date>1/1/1900
B3:B7ListIncome, Expenses, Savings
C3:C7List=INDIRECT(B3)
D3:D7Custom=ISNUMBER(D3)
B15ListActive, Inactive
 
Upvote 0
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
Excel Formula:
=IF(AND([@Type]="income",shift_late_income_status="Active",DAY([@Date])>=shift_late_income_starting_day), DATE(YEAR([@Date]),MONTH([@Date])+1,1), [@Date])
 
Upvote 0
Solution
You're welcome. Thanks for the confirmation. (y)

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
Excel Formula:
=IF(AND([@Type]="income",shift_late_income_status="Active",DAY([@Date])>=shift_late_income_starting_day),EOMONTH([@Date],0)+1,[@Date])
 
Upvote 0

Forum statistics

Threads
1,226,835
Messages
6,193,235
Members
453,781
Latest member
Buzby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top