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
excel date
-
- NewLounger
- Posts: 3
- Joined: 09 Jun 2010, 19:39
- Location: USA
Re: excel date
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))
=DATE(J1+15,H1,I1)
This is the EDIT
Or just use this ....
=DATE(YEAR(G1)+15,MONTH(G1),DAY(G1))
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: excel date
I would attack it differently, to allow the value of Years to be any number, not just 15:JIGYANSHA1985 wrote: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
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.
Sundog
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: excel date
Good approach, but I think you need to use $J$2 in your formula, rather than J2, so it readsSundog wrote:...
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.
Code: Select all
=DATE(YEAR(G2)+$J$2,MONTH(G2),DAY(G2))
StuartR
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: excel date
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.
Sundog
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: excel date
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 wrote:...The next few posts assumed that all Years values were 15; I didn't make that assumption. Thus no $ in my code.
StuartR
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: excel date
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?
Sundog
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe