Excel Date Multiply Problem

  • Thread starter Thread starter LV-Tony
  • Start date Start date
L

LV-Tony

Hello. I have billing dates and a billing frequency number that I nee
Excel to generate the upcoming billing dates for.

Example: The original billing date is 10/30/03 which is in cell C2 an
the billing frequency is 1 (for monthly) -or- 3 (for every thre
months) which is in cell M2.

How would I get Excel to return the value of 11/30/03 if the frequenc
was 1 (monthly)?

How would I get Excel to return the value of 01/30/04 if the frequenc
was 3 (every three months)?

Your help is greatly appreciated.

Tony in Las Vega
 
There are some tools in the analysis toolpak, eg EDATE(cellref, months)
function that adds a set number of months onto the cellref date. But I
dislike using the analysis toolpak unless absolutely necessary as each user
of the workbook has to have the add-in enabled (you can check for it in
VBA). Without using the toolpak, something like:

=DATE(YEAR(A1)+(MONTH(A1)+B1>12),MOD(MONTH(A1)+B1-1,12)+1,DAY(A1))

However, you have to be clear on your rules: What date do you want returned
if adding 3 months onto 11/30/2003? There is no 02/30/2004, so you would be
looking for some date in March perhaps? Similar problems occur when the
start date is 31 of month and there are only 30 days in the end month.
 
Hi

=MIN(DATE(YEAR(C2),MONTH(C2)+M2,DAY(C2)),DATE(YEAR(C2),MONTH(C2)+M2+1,0))

This formula returns same day number M2 months later, or last day of month
M2 months later, when the number of days in calculated month is less. I.e.
with M2=1:
for C2=31. January 2004 - 29. February 2004 is returned;
for C2=29. February 2004 - 29. March 2004 is returned;
etc.
 
Back
Top