SOLVED

Auto debit payment on or after set date of month

%3CLINGO-SUB%20id%3D%22lingo-sub-2817345%22%20slang%3D%22en-US%22%3EAuto%20debit%20payment%20on%20or%20after%20set%20date%20of%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817345%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20that%20will%20input%20into%20a%20cell%20a%20negative%20number%20and%20keep%20it%20that%20way%20until%20a%20set%20date%20has%20arrived%20and%20then%20change%20it%20to%20a%20positive%20number%20and%20keep%20it%20positive%20thereafter.%3C%2FP%3E%3CP%3EFor%20instance%2C%20my%20Netflix%20payment%20is%20auto-debited%20from%20my%20banking%20account%20on%20the%2015th%20of%20each%20month.%20Instead%20of%20entering%20that%20payment%20manually%2C%20a%20formula%20that%20would%20auto-enter%20that%20payment%20(keeping%20it%20negative%20until%20it%20is%20paid%2C%20keeping%20it%20positive%20after%20being%20paid)%20would%20be%20nice%20to%20have.%20I%20have%20a%20simple%20Excel%20365%20budget%20sheet%20(divided%20into%202%20six%20month%20(Jan%20-%20Jun%20%26amp%3B%20Jul%20-%20Dec)%20sections)%20to%20track%20my%20monthly%20household%20payments.%20Thank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2817345%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

I need a formula that will input into a cell a negative number and keep it that way until a set date has arrived and then change it to a positive number and keep it positive thereafter.

For instance, my Netflix payment is auto-debited from my banking account on the 15th of each month. Instead of entering that payment manually, a formula that would auto-enter that payment (keeping it negative until it is paid, keeping it positive after being paid) would be nice to have. I have a simple Excel 365 budget sheet (divided into 2 six month (Jan - Jun & Jul - Dec) sections) to track my monthly household payments. Thank you in advance!

3 Replies
best response confirmed by dlcartin (Occasional Contributor)
Solution
I would think you mean either $0.00 or the $9.99 fee (or whatever it is), not negative same amount switching to positive.
Here's a formula that refers to a date--I'd put the dates the payments are due in their own column (A4 in this example) next to the Netflix entries.
=IF(A4>TODAY(),0,9.99)
This keeps the value at zero until the date is passed the auto-payment date.
I didn' think to give the due dates their own column. I'll give this a try and see how well it works for my needs. Thank you!
It appears to be working. I have several other payments that are auto-debited from my banking account and have applied this technique & formula, and so far, so good. Thank you, again. (The reason I want to show a negative number before the payment is auto-debited from my account is because I also add all my payments for the month(s) ahead to stay within my budget.) Thanks!