Question regarding dates

[Jimmy]

I would like to know how I can denote the first day of the next month based
upon a date in another cell. For instance, if I had in cell A1 June 13, 2008
what is the formula I would write in cell A2 to show a value of July 1, 2008
by reference to A1?

Thanks,

Jimmy

 

[Max]

In A2: =DATE(YEAR(A1),MONTH(A1)+1,1)

 

[Ron Rosenfeld]

I would like to know how I can denote the first day of the next month based
upon a date in another cell. For instance, if I had in cell A1 June 13, 2008
what is the formula I would write in cell A2 to show a value of July 1, 2008
by reference to A1?

Thanks,

Jimmy

A2: =A1+1-DAY(A1)

--ron

 

[Rick Rothstein \(MVP - VB\)]

You went after the wrong month Ron.

Rick

A2: =A1+1-DAY(A1)

--ron

 

[Ron Rosenfeld]

You went after the wrong month Ron.

Rick

Funny what an adult beverage will do :-(

Should be

=A1-DAY(A1)+33-DAY(A1-DAY(A1)+32)

but now it's longer than the DATE function.

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

<sigh>



--ron

 

[Spiky]

Funny what an adult beverage will do :-(

Should be

=A1-DAY(A1)+33-DAY(A1-DAY(A1)+32)

but now it's longer than the DATE function.

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

<sigh>

--ron

If shorter is better, what about....
=EOMONTH(A1,0)+1

 

[Ron Rosenfeld]

If shorter is better, what about....
=EOMONTH(A1,0)+1

There are other parameters besides "shorter".

In pre-2007 versions, EOMONTH requires the Analysis Tool Pak to be installed.
Some IT departments don't do that, or allow it for all of their users. Also, if
that function is being used, then EVERY computer to which the spreadsheet is
distributed must have the ATP installed.

So unless you can guarantee that every computer will have the ATP installed, or
will be using Excel 2007+, it's probably better to avoid ATP functions.
--ron

 

[Spiky]

There are other parameters besides "shorter".

In pre-2007 versions, EOMONTH requires the Analysis Tool Pak to be installed.
Some IT departments don't do that, or allow it for all of their users. Also, if
that function is being used, then EVERY computer to which the spreadsheet is
distributed must have the ATP installed.

So unless you can guarantee that every computer will have the ATP installed, or
will be using Excel 2007+, it's probably better to avoid ATP functions.
--ron

Hmpf. Any computer (or user) without all of the functions is
pointless. But I bow to the bureaucracy. I'm not here to change other
companies.