Sequential dates.

[Afolabi]

I want a simple formula that will return the next month on a column. while
maintaining the day as 25th of every month.
I mean 25 Nov 2005, 25 Dec 2005, 25 Jan 2006, 25 Feb 2006 and so on....
regards.

[dvtapse]

From edit menu click fill then select series and click month so you will get dates by monthly

[CLR]

With your date in A1, put this in B1 and copy across......

=IF(MONTH(A1)=12,1&"/"&DAY(A1)&"/"&YEAR(A1)+1,MONTH(A1)+1&"/"&DAY(A1)&"/"&YEAR(A1))

This way, whenever you change the date in A1, all the other columns follow....

Vaya con Dios,
Chuck, CABGx3



"Afolabi" wrote:

> I want a simple formula that will return the next month on a column. while
> maintaining the day as 25th of every month.
> I mean 25 Nov 2005, 25 Dec 2005, 25 Jan 2006, 25 Feb 2006 and so on....
> regards.

[Afolabi]

Thanks for the prompt response. with your method, if I click and drag the
last entry, the result is not in line with my expectation. hence my need for
a FORMULA.

"Afolabi" wrote:

> I want a simple formula that will return the next month on a column. while
> maintaining the day as 25th of every month.
> I mean 25 Nov 2005, 25 Dec 2005, 25 Jan 2006, 25 Feb 2006 and so on....
> regards.

[Max]

"Afolabi" wrote:
> I want a simple formula that will return the next month on a column. while
> maintaining the day as 25th of every month.
> I mean 25 Nov 2005, 25 Dec 2005, 25 Jan 2006, 25 Feb 2006 and so on....

Another play ..

With A1 housing a commencement date,
put in B1: =DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A1),25)
Copy B1 across as far as required

To increment copying down, just change COLUMN(A1) to ROW(A1)
eg: place in A2: =DATE(YEAR($A$1),MONTH($A$1)+ROW(A1),25)
Copy A2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Afolabi]

Thanks friends, Please help modify the formula to read dd-mmm-yyyy

"CLR" wrote:

> With your date in A1, put this in B1 and copy across......
>
> =IF(MONTH(A1)=12,1&"/"&DAY(A1)&"/"&YEAR(A1)+1,MONTH(A1)+1&"/"&DAY(A1)&"/"&YEAR(A1))
>
> This way, whenever you change the date in A1, all the other columns follow....
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Afolabi" wrote:
>
> > I want a simple formula that will return the next month on a column. while
> > maintaining the day as 25th of every month.
> > I mean 25 Nov 2005, 25 Dec 2005, 25 Jan 2006, 25 Feb 2006 and so on....
> > regards.

[CLR]

Use this instead, then you can format the cells for a display of however you
wish.

=IF(MONTH(A1)=12,1&"/"&DAY(A1)&"/"&YEAR(A1)+1,MONTH(A1)+1&"/"&DAY(A1)&"/"&YEAR(A1))*1

Vaya con Dios,
Chuck, CABGx3



"Afolabi" wrote:

> Thanks friends, Please help modify the formula to read dd-mmm-yyyy
>
> "CLR" wrote:
>
> > With your date in A1, put this in B1 and copy across......
> >
> > =IF(MONTH(A1)=12,1&"/"&DAY(A1)&"/"&YEAR(A1)+1,MONTH(A1)+1&"/"&DAY(A1)&"/"&YEAR(A1))
> >
> > This way, whenever you change the date in A1, all the other columns follow....
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Afolabi" wrote:
> >
> > > I want a simple formula that will return the next month on a column. while
> > > maintaining the day as 25th of every month.
> > > I mean 25 Nov 2005, 25 Dec 2005, 25 Jan 2006, 25 Feb 2006 and so on....
> > > regards.

[Afolabi]

Thanks Max, it works! I only replaced "COLUMN" or "ROW" with +1 to get the
right results .
i.e
=DATE(YEAR(A1),MONTH(A1)+1,25)

Thanks once more.
"Max" wrote:

> "Afolabi" wrote:
> > I want a simple formula that will return the next month on a column. while
> > maintaining the day as 25th of every month.
> > I mean 25 Nov 2005, 25 Dec 2005, 25 Jan 2006, 25 Feb 2006 and so on....
>
> Another play ..
>
> With A1 housing a commencement date,
> put in B1: =DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A1),25)
> Copy B1 across as far as required
>
> To increment copying down, just change COLUMN(A1) to ROW(A1)
> eg: place in A2: =DATE(YEAR($A$1),MONTH($A$1)+ROW(A1),25)
> Copy A2 down as far as required
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

[Max]

Glad you got it adapted to suit <g> !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Afolabi" wrote:
> Thanks Max, it works! I only replaced "COLUMN" or "ROW" with +1 to get the
> right results .
> i.e
> =DATE(YEAR(A1),MONTH(A1)+1,25)
>
> Thanks once more.