Assistance in updating formula for DATE functions - eg DateValue

To Whom It May Concern

I would like some assistance writing/amending a formula, that uses all three

* DATEVALUE

* IF(AND(OR(

and

"AND"

functions.

I've attached a spreadsheet (containing only the necessary data, nothing else), however, I will explain how I intend to use it.

Firstly, two Sheets are in the attachment: (titled), "Income & Cash or Debit Expenses" & "Pharmaceuticals List".

On the "Income & Cash or Debit Expenses" sheet, go to Cells A7 and/or B7.

In Cell A7, the formula looks like this:

=(IF($B$7=(((((""))))), (((((""))))), (IF('Income & Cash or Debit Expenses'!$A$1=(((((""))))), (((((""))))), (IF(DAY('Income & Cash or Debit Expenses'!$A$3)<>365, (IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={3,4,5,6,7,8,9})), "Food and Groceries*; * Maximum spend per week, $100.00, and Medicines/Pharmacy (Refer: 'Pharmaceuticals List' sheet, for more information.), (Add extra, $50.00*2/f cash withdrawal for Adelaide Oval Cash Card (incorporating AFL & AFLW))", ((IF('Income & Cash or Debit Expenses'!$A$1=(((((""))))), (((((""))))), (IF(DAY('Income & Cash or Debit Expenses'!$A$3)<>365, (IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={8,9,10,11})), "Food and Groceries*; * Maximum spend per week, $100.00, and Medicines/Pharmacy (Refer: 'Pharmaceuticals List' sheet, for more information.), (Add extra, $50.00*2/f cash withdrawal for Adelaide Oval Cash Card (incorporating AFL & AFLW))", ((IF('Income & Cash or Debit Expenses'!$A$1=(((((""))))), (((((""))))), (IF(DAY('Income & Cash or Debit Expenses'!$A$3)<>365, (IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={1,2,12})), "Food and Groceries*; * Maximum spend per week, $100.00, and Medicines/Pharmacy (Refer: 'Pharmaceuticals List' sheet, for more information.), (Add extra, $50.00/f cash withdrawal for personal use.)", ("")))))))))))))))))))))))

and, in Cell B7, the formula looks like this:

=(IF('Income & Cash or Debit Expenses'!$A$1=(((((""))))), (((((""))))), (IF(DAY('Income & Cash or Debit Expenses'!$A$3)<>365, (IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={3,4,5,6,7,8,9})), (SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2)))*'Income & Cash or Debit Expenses'!$B$5), (IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={8,9,10,11})), (SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2)))*'Income & Cash or Debit Expenses'!$B$5), (IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={12,1,2})), (SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)))*'Income & Cash or Debit Expenses'!$B$5)))))))))))

From this point, here's what I want to do, and would like assistance with:

In Cell B7, I want to amend the formula above, to recalculate to show ONLY on/between a specific date period. For example:

=IF(DATEVALUE>={13/4}, (SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*8)))*'Income & Cash or Debit Expenses'!$B$5), IF(DATEVALUE<={16/4}, (SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*8)))*'Income & Cash or Debit Expenses'!$B$5), AND (IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={3,4,5,6,7,8,9})), (SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*8)))*'Income & Cash or Debit Expenses'!$B$5)

[HINT: Change "ONLY" (30*2) to show (30*8), to occur ONLY between dates 13/4 - 16/4 inclusive.]

So, in other words, the formula will be =(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*8))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*8)))*'Income & Cash or Debit Expenses'!$B$5), only when date value is =>{13/4} or <={16/4}.

Otherwise, if date is NOT specifically between (dates) 13/4 - 16/4 (inclusive), BUT is still within the MONTHS (of) {3,4,5,6,7,8,9}, the calculation is (then) =(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2))+(SUM(100*2)+(SUM('Pharmaceuticals List'!$G$39)+(25*2)+(30*2)))*'Income & Cash or Debit Expenses'!$B$5)



Footnote (1): Where an asterisk (*) appears before/after a word, eg "Food and Groceries*; * Maximum (etc)", in this context, the asterisk (*) is NOT referring to the "multiply" function.

Footnote (2): "*'Income & Cash or Debit Expenses'!$B$5" (in this context, the asterisk (*) IS referring to the "multiply" function. (Multply (by) SheetName (and) Cell. [Manually change 'Income & Cash or Debit Expenses'!$B$5, to read either 0% or 10%, and this will change automatically on all other sheets. In Australia, the "Goods and Services Tax (GST)" is set at a maximum of 10%.]

Footnote (3): In Australia, the date is written using the DD/MM/YYYY format, so as per the date example above, 13 April (DD/MM only), is written as 13/4, NOT 4/13 (April 13; US/international format).

Thank you to anyone who assists me with this, and I have attempted to explain this as best I can; I apologise in advance for any confusion. Your assistance is most graciously welcomed and appreciated.

Matthew

By the way, I'm having trouble uploading the attachment. Please help.