I recently posted with my attempted formula but all iterations of everything I'm trying just aren't working, so I'm asking with a blank slate here. I simply have cells that either have the # 1 in them or the # 15 in them and I want the cells next to them to calculate a billing amount based on if the day of the month corresponds to the 1st or the 15th. However, the catch is that, the 1st and the 15th don't always fall on a business day, so I want the formula to look at today's date +/- a day or two and if today is within a day or two of the 1st then all customers that we invoice on the 1st will populate those cells and the ones we bill on the 15th remain blank. I will enter below some of the formulas I've tried that just haven't worked. Some of these formulas came from help I received from you guys on here. Thank you so much for that.
F1 Cell has either 1 or 15 in it and the B1 and C1 cells contain the annual billing divided the number of months we bill them.
=IF(and(DAY(TODAY())<=F1+3),day(today()>=F1-3),"B1/C1",""))))
=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,"")
=IFERROR(IF(AND(DAY(TODAY())<=F3+3),DAY(TODAY()>=F3-3),QUOTIENT(B3,C3)),"")
-- this one almost worked but it did it backwards - today is the 15th and it calculated all numbers for the 1st and left the 15th billing cells blank
There are other things I've tried, but they included more columns that i've since removed.
Anyone with thoughts on how to help me achieve this, I would be so grateful. It seems like there has to be a simple, simple answer out there. If today is the one or two days before or after the 1st, do some math, if not, be blank.
If today is one or two days around the 15th, do some math, if not, be blank.
It's just gotten so complex! Thanks in advance anyone who might have an answer for me.
