Monthly Recuring charges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dk - 10 Oct 2004 03:51 GMT

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

Reply to this Message

Frank Stone - 11 Oct 2004 18:06 GMT

hi,
you might find conditional formating.
see this site for more info
http://www.contextures.com/xlCondFormat01.html

>-----Original Message-----
>how can we setup a monthly recuring charge for ex: we have a sheet that we
[quoted text clipped - 4 lines]
>month) we have a date field in the spreadsheet
>.

Reply to this Message

swatsp0p - 11 Oct 2004 20:09 GMT

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

--
swatsp0

Reply to this Message

dk - 12 Oct 2004 15:35 GMT

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

> I'm not sure this is a conditional formatting issue.
>
[quoted text clipped - 17 lines]
>
> HTH

Reply to this Message

swatsp0p - 12 Oct 2004 16:41 GMT

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,

Signature

swatsp0p

Reply to this Message

dk - 12 Oct 2004 22:55 GMT

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")

> re: -do we have to make a whole calendar of the complete year or only
> the first of
[quoted text clipped - 18 lines]
>
> Good luck,

Reply to this Message