Too Many arguments

Occasional Contributor
2022 PriceMosStart DateEnd DateBill Day
$62,560.0084/1/202111/30/231
$24,381.0074/15/202011/15/2215

=IF(and(DAY(TODAY())<=F1+3),day(today()>=F1-3),"B1/C1",""))))

 

I keep getting the error, "Too many arguments." I want the end to do the calculation if today is +or - 3 days before or after the 1st or the 15th of the month. I've tried so many iterations of this. This is the closest I've come, but I still can't get it to work. I'm somewhat new to all of these conditional formatting statements. I would really like something that does a lot more, but I suppose that question is for a different day. I just would really appreciate someone helping get this to calculate the bill if it's around the 1st of the month or the 15th. If it's not one of those, then I want it to be blank. Thank you in advance to anyone who can help me with this.

 

15 Replies

@JLMWork1625 

I had an email from Quadruple_Pawn but his response isn't here. I'm not sure how this works, I guess, so I'm copying it here.

Quadruple_Pawn (Valued Contributor) mentioned you in a post! Join the conversation below:

@JLMWork1625 

=IF(OR(AND(DAY(TODAY())>DAY(D1)-3,DAY(TODAY())<DAY(D1)+3),AND(DAY(TODAY())>DAY(D1)-3,DAY(TODAY())<DAY(D1)+3)),B1/C1,"")

Is this what you are looking for? 

 

Thank you so much for this, but it didn’t quite do what I wanted it to. I so appreciate your effort though. If it worked it would have filled in cells whose bill date is tomorrow the 15th since the 14th is within the plus or minus 3 days of the 15th. It didn’t do that. I got several errors when I pulled it down and it put some answers in the ones with bill dates on the 1st. Thanks again for your help though!

@JLMWork1625 

= IF(
    AND(
        DAY(TODAY()) <= F1+3,
        DAY(TODAY()) >= F1-3
    ),
    B1/C1,
    ""
  )

This is just the result of placing the formula into the AFE to check syntax.

Thank you, Peter. That still isn't working.

@JLMWork1625 

I only set out to produce a formula that evaluates.  Whether it does anything useful is another matter!

@JLMWork1625 

Sorry for the delayed reply. Just after i had posted a reply in this discussion i realised that my suggested formula doesn't work. Now i hope that i can help a little with a formula that should work for the 15th of the month. For the 1st unfortunately i can't make a suggestion because the days before the 1st can be 31st and 30th or 30th and 29th or even 28th and 27th and there are leap years to make this more difficult.

Well, Quadruple Pawn, you're amazing! You got it to work in your spreadsheet but when I moved it over to mine, I can't get anything to happen. I might just be throwing in the towel on this one or maybe I need to start from scratch. I feel bad even putting things out there on this and asking people for help. Ughh. I suppose this is a little like writing code, which I don't know how to do, and I'm trying to skip 101. Thank you all for trying to help me! I really appreciate it.

2022 Price(B1) | Mos(C1) | Start Date(D1) | End Date(E1) | Bill Day(F1) | Formula(G1)
$62,560.00               8           4/1/2021          11/30/2023           1          Formula in G2
$24,381.00               7           4/15/2020         11/15/2022         15       Fill down from above
Formula in G2 as below.
=IF(AND(F2=1,OR(AND(TODAY()>=EOMONTH(TODAY(),-1)-2,TODAY()
<=EOMONTH(TODAY(),-1)+4),AND(TODAY()>=EOMONTH(TODAY(),0)-2,TODAY()<=EOMONTH(TODAY(),0)+4))), B2/C2, IF(AND(F2=15,AND(DAY(TODAY())>=12,DAY(TODAY())<=18)),B2/C2,""))

Starrysky, thank you so much for this. I am curious, did you try this in excel by chance? There are no errors in this formula, but all the cells remain blank. And your formula seems to be so close because I do really need something that covers the 1st, and the 2 or 3 days before or after it and the 15th etc. If I understood more, there's probably a tweak that would make it work.
I'm not sure that today is 18 or 19 in your country. In my country, today is 19 Mar 22.
So for me, today is not +-3 days from 15 (12-18) and it is not +-3 days from 1st day of the month and we can says it in other way that not between -2 from end of month and +4 from end of month.
In this case, all the result will be blank.
I wrote the formula based on your work flow and the value in F column (whether 1 or 15). I just wonder if you want to consider 1 and 15 together that mean 2 times Bill for 1 row of data. If so, please let me know.
I see what you're saying. So the formula should work if it's +or- 3 days from either the 1st or the 15th?
it is the 18th here
It depends on what day we are choosing in F column (either 1 or 15).
As you said today is 18 and if you choose 15 in F3, the formula will show the result in Row 3. But you choose 1 in F2, the result will be blank in Row 2. So, if you want to get the result in Row 2 as in Row 3, you need to change from 1 to 15 in F2.

My understanding is that
either 1 or 15 (+-3 days from (1 or 15))
is only for 7 days and
1 and 15 ( +-3 days from (1 and 15))
is for 14 days.
I wonder which one you want.
The first one, 1 or 15.
For example, if it's the 12th, 13th, 14th, 15th, 16th, 17th, 18th, then calculate.
This is the tricky one because the months don't all have 30 days. If it's the last two days of the month, the 1st, 2nd, 3rd, or 4th, then calculate.
I can't thank you enough for trying to help me with this. I just have no idea, but I'm determined to start learning. I can see how useful it is to understand how to make this stuff work.

@JLMWork1625 

I haven't followed this discussion closely, but an idea for a condition that determines proximity to the 1st and 15th of a month might be

= ISEVEN(1+QUOTIENT(DAY(TODAY()+9),7))

 

Wow your logic here is very great! I really love it.
Actually, you don't even need to add +1 and use "iseven" function in the formula.
=ISODD(QUOTIENT(DAY(A1+9),7)) will be ok as well.
The formula is condisered for both conditions of +-3 days from day 1 and +-3 days from day 15 and it shall always be correct for all months with 31 days.
For other months those have 30 days, 28 is required to add and for February with 28 or 29 days (leap year), 26-28 or 27-29 are required to add. So, we need to combine with another formula.
I know how to split the formula if we only want for 7 days that means either +-3 days from day1 or day 15. That's amazing.