Need a formula to calculate the end date in refference to the start date

[suciulaurentiu]

So if i have for example: 30.10.2014 as start date of a period of time, and i have a contract for let`s say 4 years, regarding a club policy, the end date should be 29.11.2018. How can i calculate this automaticly within a formula ?

Please help me out a little.

[NGBFLNG1]

This formula example calculates 4 years (48 months) to whatever is in cell H14. Paste it into whatever cell you want and change the $H14 to whatever cell has the date.

=IF($H14=""," ",EDATE($H14,48))

[suciulaurentiu]

Thank you very much for the quick reply, but it still doesn`t give me the value of 29.11.2018
Should i put the clause Month() +1 and day () -1 ?

" =IF($G$9=""," ",EDATE($G$9-1,48)" still i haven`t sorted the month

This might do the trick : =IF($G$9=""," ",EDATE($G$9-1,49)) correct ?

[MrShorty]

I think the first thing to do is review something like this http://www.cpearson.com/excel/datetime.htm to understand how Excel handles dates and times.

Is this 30.10.2014 the actual way that the data are entered? Is Excel seeing this as a text string or is it actually recognizing it as a date? In my version (2007), even with a cell formatted as dd.mm.yyyy, it does not seem to want to recognize such an entry as a date, preferring to store it as text. As a text string, it will be more difficult to work with, because we will not be able to use Excel's built in calendar to perform the calculation. Perhaps 2013 is better at recognizing alternative date formats, but mine isn't.

If you decide to use Excel's built in calendar, 4 years from a given date should simply be 30.10.2014 + 3*365 + 1*366 (to account for a leap year) or however many days you decide 4 years should be. If you and excel decide to leave these dates as text strings, then you will need a text formula to extract the year (perhaps =right(cell,4)) then add 4 to it, then concatenate the new year back into the text string.

[suciulaurentiu]

I am just using date formatting on the cell, every employee enters the date as " 30.10.2014" so i believe the above calculation is correct. I am using excel 2013 yes

[polkev]

Excel stores dates as numbers (e.g. October 30, 2014 is stored as 41942) Your dates may be stored as text; if so, they cannot be used for calculations. If you pres <Ctrl `> (the character just to the left of the 1 on a standard American keyboard) it will reveal the formulas and remove the formatting on your spreadsheet; doing the keystroke again will put it all back. If your turn into numerical values, then you use any of the formulas above. If not, you'll have to convert your dates from text to Excel recognized dates.