if function or what ?

  • Thread starter Thread starter shital
  • Start date Start date
S

shital

I have field like
A1-Date of issue
A2-No. of Year
A3-Mode of Payment.

What i want to do is When i type no. of year and mode
(yly,Hly,Qty or month)if i type yly mode i should give
every yearly date from date of issue up to no. of year
enter in no. of year field.

it should like in
A5 1-11-03
A6 1-11-04
A7 1-11-05
A8 1-11-06
upto no.of year.

Please help me.

Shital
..
 
Put this in A5 and copy down as far you think you'll need (meaning if A2 will never be more than 50, drag down to A55

=IF(ROW()-4>$A$2,"",DATE(YEAR($A$1)+ROW()-4,MONTH($A$1),DAY($A$1))

HT
Jaso
Atlanta, G
 
Create a lookup table that contains the number of payments, and date
increases for each payment mode:

Mode Payments IncYr IncMth
Yearly 1 1 0
Half-yearly 2 0 6
Quarterly 4 0 3
Monthly 12 0 1

Name this range ModeList.

In cell A3, choose Data>Validation, and create a list using the first
column of the ModeList range as a source.

In cell A5, enter: =A1
In cell A6, enter:
=IF(ROW()<=5+$A$2*VLOOKUP($A$3,ModeList,2,0),
DATE(YEAR(A5)+VLOOKUP($A$3,ModeList,3,0),MONTH(A5)+VLOOKUP($A$3,ModeList,4,0),DAY(A5)),"")

Copy this formula down as far as required for the maximum number of
years allowed, e.g. 120 rows if 10 years is the maximum (10 years x 12
monthly payments)
 
It looks to me like you could use a VLOOKUP function to assign values to
your "modes" and then use standard formulas in A5 and below cells.......such
as assign 365 to your mode "yly"........then in cell A5 you could have the
formula =A1, and in cell A6 the formula
=A5+vlookup(A3,ModeTable,2,false).....note the value in "ModeTable,2" would
be 365 in this case.......

hth
Vaya con Dios,
Chuck, CABGx3
 
Thanks for reply it's working good for yly mode
but if i change mode of payment yly to hly or qty or
monthly it's not working.
-----Original Message-----
Put this in A5 and copy down as far you think you'll need
Click to expand...
(meaning if A2 will never be more than 50, drag down to
A55:
 
Back
Top