Assistance in updating formula for DATE functions - eg DateValue

[matthewhinds]

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.

[Glenn Kennedy]

Death by brackets!

Those formulae are hugely overcomplicated by the overuse of ().

The formulae in the purple cells have been changed.

Your description for B7 is incomplete.

If date is between 15/4 and 16/4, use 8 as the multiplier.
If date is Apr-October, use 2 as the multiplier,
Otherwise, something else....

Interim formula:

=IF($A$1="","",IF(AND(MONTH($A$3)>=3,MONTH($A$3)<=9),(1+B5)*SUM(100*2+(SUM('Pharmaceuticals List'!$G$39+25*2+30*IF(AND($A$3>=DATE(2023,4,13),$A$3<=DATE(2023,4,16)),8,2)))),"Something else"))

Please explain the "something else". I found it impossible to work my way through your multiple layers of ()

[matthewhinds]

Hi Glenn

Thank you for your reply to my post; much appreciated.

Firstly, I have learnt that Excel forces me to use "excessive" brackets (), as it won't accept the formula (I'm entering), otherwise, given that the majority of my formulas in my spreadsheets are built using "IF" statements, and therefore, some functions are automated. I am limited in my experience with Excel and what you see, is based soley on what i have studied/learnt over time.

If you can offer a more "tidy" or concise way to write my formulas, then I welcome any assistance you are willing to offer. Thank you.

Additionally, I don't know what you refer to as "something else". Sorry, but this was not in my original formula when I attached my file to this post.

Thank you, for what you have offered me, I appreciate it.

Matthew

[TMS]

In terms of formula reduction,

Please Login or Register  to view this content.

And:

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

[TMS]

Change 30*2 to IF(AND(MONTH(A3)=4, DAY(A3)>=13, DAY(A3)<=16), 30*8, 30*2)

[TMS]

Is this resolved now?

[matthewhinds]

Yes, it is. Thank you to you and all whom have assisted me.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[matthewhinds]

Already done.

[TMS]

You're welcome. Thanks for the rep.

I would suggest you put the amendment into my formula, rather than yours.

Please Login or Register  to view this content.

If you want to make this a learning experience, some pointers:

1. Don't put a bracket at the beginning of a formula. It just means you have to put one at the end.
2. Adding lots of brackets adds no value: IF('Income & Cash or Debit Expenses'!$A$1=(((((""))))), (((((""))))) is the same as IF('Income & Cash or Debit Expenses'!$A$1="", ""
3. Don't put brackets round a multiplication: (30*2) is the same as 30*2
4. Don't use SUM when you don't need to. If there is only one parameter, you don't need it. If you are using plus signs inside the brackets, you don't need it. SUM takes a number of comma separated values. If you don't have a list of comma separated values, you don't need it.
5. The AND in your IF(AND(OR(...))) in your formula is redundant. You don't have two conditions to AND together, although the OR is appropriate.
6. If the formula refers to a cell/range on the same worksheet, don't fully qualify it with the sheet name. IF('Income & Cash or Debit Expenses'!$A$1="", "" is the same as IF($A$1="", ""
7. There is also some redundancy in your formula. IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={3,4,5,6,7,8,9})) and IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={8,9,10,11})) have some overlap and the same outcome. They can be combined (as I did): IF(AND(OR(MONTH('Income & Cash or Debit Expenses'!$A$3)={3,4,5,6,7,8,9,10,11}))

As Glenn said, overuse of brackets just makes the formula difficult to read ... and very long. Your original formula was 835 characters long. My tidied up version was 175 characters long. The adjusted formula is 229 including the date check.