Question about MONTH Calculations

[shdawson]

Hi,


I have some reporting requrements, and want to simplify the date ranges. By this statement, I mean that the on-going date range of months changes.

My goal is to have a start date, then add successive months to that as necessary. Sometimes the goal is 3-months, sometimes it is 3-months in the past and 3-months in the future.

Right now, I have simple logic:
Start 05/01/2011 12:00:00 AM
End 05/31/2011 11:59:59 PM


From there, I manually populated some other cells:
12/01/2010 12:00:00 AM
01/01/2011 12:00:00 AM
02/01/2011 12:00:00 AM
03/01/2011 12:00:00 AM
04/01/2011 12:00:00 AM
05/01/2011 12:00:00 AM
06/01/2011 12:00:00 AM
07/01/2011 12:00:00 AM
08/01/2011 12:00:00 AM
09/01/2011 12:00:00 AM
10/01/2011 12:00:00 AM
11/01/2011 12:00:00 AM
12/01/2011 12:00:00 AM



I do not want to make a mountain out of a mole hill. Perhaps I should do the manual effort and leave it at that.

My question is thinking about the MONTH function. Is there a simple function for MONTH + 1? That would avoid the number of days in a month (28, 29, 30, or 31), and always keep the start time at midnight, to the second.

Thank you, kindly....
SHD

 

[Scott Huish]

I'm actually subtracting a minute. If you need it more precise than that:

=EOMONTH(J$3,0)+1-"0:00:01"

Format as:
m/d/yy h:mm:ss AM/PM

 

[markmzz]

Shdawson,

Or try this (without load the Analysis Toolpak):

=DATE(YEAR(A1),MONTH(A1)+1,1)-"00:00:01"

Markmzz

 

[shdawson]

Thank you, Mark.


Hotpepper, yes...I do need to account for the entire last minute (59). Thank you for clarifying.


Kindly,
SHD