Mar 14 2022 10:57 AM
2022 Price | Mos | Start Date | End Date | Bill Day |
$62,560.00 | 8 | 4/1/2021 | 11/30/23 | 1 |
$24,381.00 | 7 | 4/15/2020 | 11/15/22 | 15 |
=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.
Mar 14 2022 12:14 PM
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:
=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!
Mar 14 2022 12:18 PM
= 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.
Mar 14 2022 12:58 PM
I only set out to produce a formula that evaluates. Whether it does anything useful is another matter!
Mar 17 2022 09:29 AM
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.
Mar 17 2022 10:52 AM
Mar 18 2022 04:10 AM - edited Mar 18 2022 04:12 AM
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,""))
Mar 18 2022 10:16 AM
Mar 18 2022 11:20 AM
Mar 18 2022 11:30 AM
Mar 18 2022 11:57 AM
Mar 18 2022 12:07 PM
Mar 18 2022 02:09 PM
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))
Mar 18 2022 06:25 PM