Monthly Recuring charges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

how can we setup a monthly recuring charge for ex: we have a sheet that we
keep track of all our expenses we have a telephone bill charge every 13th of
the month for $25 put it we don't use everyday the spreadsheet or we may use
the the excel spreadsheet twice a day we want that this charge shall
automatically be entered on or after the 13th of of the month (only once each
month) we have a date field in the spreadsheet
 
I'm not sure this is a conditional formatting issue.

Assuming you have column e.g. "A") with the current month/year, the
enter this formula in a corresponding cell:

=IF(TODAY()>=DATE(YEAR(A1),MONTH(A1),13),25,"Not Yet")

each row in the Today column needs to have the date of the desire
month as:

A* B**
1 10/1/2004 $25.00
2 11/1/2004 Not Yet
3 12/1/2004 Not Yet
4 01/1/2005 Not Yet
5 *Feb-05 Not Yet

*This column could be formatted as simply mm/yy if desired
** This column should be formatted currency

HT
 
do we have to make a whole calendar of the complete year or only the first of
the month, also does each entry have to use the today() or only these rows
because we realized that when today() is used when you make any changes to
that row no matter which colmun the date changes and we wouldn't like to
change the date of original entry for ex: we have ented a charge for $20 on
8/09 then we received a credit from merchant we want to modify the sum but
not the date (this has no relation to our original question, but it's only
related about putting in today() as your answer
 
re: -do we have to make a whole calendar of the complete year or only
the first of
the month-,

No, just the first of every month. The cell that contains this entry
is then referenced in your formula throughout that month's entry thus:

=IF(TODAY()>=DATE(YEAR(*A1*),MONTH(*A1*),13),25,"Not Yet").

You could enter the date in just one cell and reference that cell,
leaving rows below it blank until the next month, or have that date
(e.g. 10/1/2004) as an entry in every row

Then, only the required "fixed" date need be changed (the "13" in my
example) for each affected row.

If the dollar amount changes (the "25" in my example), you would then
need to modify the formula with the corrected amount. You had asked
for a "fixed" amount, not a variable amount. I assume "credits" would
be the exception, not the rule.

Good luck,
 
we copied (drag down) the formula to the next row down in the column but it
came up the same firgure even though we changed that date to 10/15/2004 and
we have already a charge this month by the first cell we created for today
date
=IF(TODAY()>=DATE(YEAR(D21),MONTH(D21),12),57.62+6.2,"Not Yet")
 
Back
Top