# working with dates in formulas

1. ## working with dates in formulas

Hi,
I have a certain date, e.g. 25/03/2011 in column A. In column B, I would like to have a formula that gives the same date, e.g. 5 years later. In the example, it would be 25/03/2016.
In column C, I would like to have a formula that gives the first of January of the year following the year in column B. So that would be 01/01/2017.

How do I do this ?
Thanks !

2. ## Re: working with dates in formulas

First part:

=EDATE(A1,60)

second part:

=DATE(YEAR(A1)+6,1,1)

you may need ; not , as the separator

3. ## Re: working with dates in formulas

=EDATE(a1,60) for the date in 5 years time in b1
=DATE(YEAR(b1)+1,1,1) in c1

4. ## Re: working with dates in formulas

Assuming your original date is in A2, put this in B2:
Formula:
`Please Login or Register  to view this content.`

then this in C2:
Formula:
`Please Login or Register  to view this content.`

Edit: I obviously type more slowly than the others...

5. ## Re: working with dates in formulas

In A1 Cell
25-03-2011

In B1 Cell
=EDATE(A1,60)

In C1 Cell
=EOMONTH(EDATE(B1,12),-MONTH(B1))+1

6. ## Re: working with dates in formulas

Hi Jokkebal,

1st Formula :- In order to get the date of 5 years later, use this formula =DATE(YEAR(A4)+5,MONTH(A4),DAY(A4))

2nd Formula:- To get the date of 1st January for following year, use this formula =DATE(YEAR(B4)+1,1,1)

Instruction:- If you have date in A column then use 1st formula in column B and 2nd formula in column C.

7. ## Re: working with dates in formulas

it works, thanks to all !

9. ## Re: working with dates in formulas

you're welcome!!

10. ## Re: working with dates in formulas

You're welcome, glad we could help and thanks for the rep.

There are currently 1 users browsing this thread. (0 members and 1 guests)