Find the date 6 months later

[Guest]

I want a formula that calculates 6 months later then a certain date, eg:

27-08-07, 6 months later, like 27-02-08. When i use the formula
date(year(27-08-07);month(27-08-07)+6;day(27-08-07)), excel returns #num.
this only works in the same year.
Can you help me?

 

[Beege]

I want a formula that calculates 6 months later then a certain date, eg:

27-08-07, 6 months later, like 27-02-08. When i use the formula
date(year(27-08-07);month(27-08-07)+6;day(27-08-07)), excel returns #num.
this only works in the same year.
Can you help me?

Filipemr

How's about =A1+180, where A1 would be your date (six months earlier)?

Beege

 

[Peo Sjoblom]

Why are you typing in the date in the formula, use a cell reference instead

=DATE(YEAR(A1);MONTH(A1)+6;DAY(A1))

otherwise you might as well use

=DATE(2007;8+6;27)

 

[Guest]

The formula should work, try typing the date in a different cell and using
that reference instead of the date string, something like:
=date(year(A1);month(A1)+6;day(A1))
where A1 has your date.

Hope this helps,
Miguel

 

[Guest]

With a date in A1 try:-
=DATE(YEAR(A1), MONTH(A1)+6, DAY(A1))

Mike

 

[Guest]

I've tried to put the date in a diferent cell, and use the reference off the
cell. Can it be a problem of format?

"Miguel Zapico" escreveu:

 

[Guest]

Yes, it can be a problem with the format: the year, month and day function
expect a serial number, that is how the dates are stored, and they don't
recognize the string 27-08-07.
If you don't want to use cell references, Peo's solution with numbers is the
clearer one.

Hope this helps,
Miguel

 

[Ron Rosenfeld]

I want a formula that calculates 6 months later then a certain date, eg:

27-08-07, 6 months later, like 27-02-08. When i use the formula
date(year(27-08-07);month(27-08-07)+6;day(27-08-07)), excel returns #num.
this only works in the same year.
Can you help me?

In order for Excel to interpret 27-08-07 as a date, your Windows Regional
Settings must have dd-mm-yy as the format.

If your regional format is set up like that, then you "could" use the formula:

=date(year("27-08-07");month("27-08-07")+6;day("27-08-07"))

But you would be better off simply putting the date in some cell, and referring
to that cell:

=date(year(a1),month(a1)+6,day(a1))

Of course, you may have a problem looking for 6 months after, for example, 31
Aug 2007. Your formula will result in 2 March 2008, since there are not 31
days in February.

You need to decide what you want to do in those kinds of circumstances.


--ron

 

[Bob Phillips]

=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dana DeLouis]

Just something to keep in mind depending on what you want...

If the date is 10/31/07,
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
goes forward to 5/1/08

=EDATE(A1,6)
goes backward to 4/30/08

Again, depends on your needs.

 

[Guest]

Like you see, it doesn't work. My version is in Portuguese:

39301 =DATA(ANO(G29);MÊS(G29)+6;DIA(G29)) (this is the result of excel)

39301 is the serial number that correpoonds to the date 7-08-07

Ano=Year
Mês=Month
Dia=day

 

[Bob Phillips]

Don't you just need to format it as a date?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)