excel date

[JIGYANSHA1985]

Sir,
On column G, start_date is a field and at column J, Years is a field.

I Would like reflect at Column K (Field name is Fin_Date), start_date+years. For ex. 25/07/2000 + 15 = 25/07/2015

Regards
Jigyansha

[tfspry]

Assuming Col H has Month and Col I has day ... try this in Col K .....
=DATE(J1+15,H1,I1)

This is the EDIT
Or just use this ....
=DATE(YEAR(G1)+15,MONTH(G1),DAY(G1))

[JIGYANSHA1985]

Thanks Sir

[Sundog]

Sir,
On column G, start_date is a field and at column J, Years is a field.

I Would like reflect at Column K (Field name is Fin_Date), start_date+years. For ex. 25/07/2000 + 15 = 25/07/2015

Regards
Jigyansha

I would attack it differently, to allow the value of Years to be any number, not just 15:

1) Format Column G as Date => More number formats => Locale = <chose a locale that corresponds to your date format of dd/mm/yyyy, such as Assamese (India)>.
2) Say row 2 has the information you want to convert; then G2 = 25/07/2000 (formatted as above), and J2 = 15 (or any other number).
3) Then in K2, insert the formula =DATE(YEAR(G2)+J2,MONTH(G2),DAY(G2)).
4) This will cause K2, Fin_Date, to contain a date that is J2 Years after G2 Start_Date, no matter what value is in J2 Years.
5) Copy the formula in K2 down to the bottom of your data.

[StuartR]

...
1) Format Column G as Date => More number formats => Locale = <chose a locale that corresponds to your date format of dd/mm/yyyy, such as Assamese (India)>.
2) Say row 2 has the information you want to convert; then G2 = 25/07/2000 (formatted as above), and J2 = 15 (or any other number).
3) Then in K2, insert the formula =DATE(YEAR(G2)+J2,MONTH(G2),DAY(G2)).
4) This will cause K2, Fin_Date, to contain a date that is J2 Years after G2 Start_Date, no matter what value is in J2 Years.
5) Copy the formula in K2 down to the bottom of your data.

Good approach, but I think you need to use $J$2 in your formula, rather than J2, so it reads

Code: Select all

=DATE(YEAR(G2)+$J$2,MONTH(G2),DAY(G2))

[Sundog]

Stuart, my assumption was that the J column could have any number in it. The author of the spreadsheet would have to respond to this, but he never indicated that 15 was anything more than an example of a Years value. The next few posts assumed that all Years values were 15; I didn't make that assumption. Thus no $ in my code.

[StuartR]

...The next few posts assumed that all Years values were 15; I didn't make that assumption. Thus no $ in my code.

Ah, so you would have to copy J2 down to the bottom of the data, as well as copying K2 down to the bottom of the data.

[Sundog]

Huh? My assumption is that each row would have a Start_Dale (column G) value and a Years (column J) value. Why would I copy J2?

[StuartR]

Ah, now I understand what you were doing. thank you.