Trying to build a formula
 

I am trying to construct a formula to return the first day of the next month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?

Try =DATE(YEAR(A1),MONTH(A1)+1,0)+1
The first part =DATE(YEAR(A1),MONTH(A1)+1,0) find the last day of the month
of the date in A1. Adding one does what you want.

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"faberk" wrote in message
...
I am trying to construct a formula to return the first day of the next
month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?

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

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to construct a formula to return the first
day of the next month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the
month function and
increment it by one. This works fine until i run into a
date in december.
How can i do this?

.

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"faberk" wrote in message
...
I am trying to construct a formula to return the first day of the next
month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?

Hi,

Running in Excel 2002,
=DATE(YEAR(A1),MONTH(A1)+1,1)
where A1 is the existing date, works fine.

If that doesn't work, then I'd probably do something like
=IF(MONTH(A1)=12, DATE(YEAR(A1)+1,1,1), DATE(YEAR(A1), MONTH(A1)+1, 1)

You could do something within the date function itself, but this is more
obvious (at least to me).

Regards,

David Jessop

"faberk" wrote:

I am trying to construct a formula to return the first day of the next month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?

Assuming the date is in A1, use

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"faberk" wrote in message
...
I am trying to construct a formula to return the first day of the next
month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?