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

 

[number kruncher]

The month function will add 1 month, as will other date functions, eg.

Source date in A1

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

Amend as required

 

[shdawson]

I see.

=DATE(YEAR(C3)+0,MONTH(C3)+0,DAY(C3)+0)


So, simply leave +0 for YEAR and DAY.


Awsome, thank you very much.



Kindest Regards,
SHD

 

[number kruncher]

Welcome.

Thanks for the feedback.

 

[shdawson]

Hi,


Further question, please.

Say I start on 6/1/2011. I can see how to calc for 1-month, to get me to 7/1/2011. Working with other reporting aspects, so need an end date. Looking for the last calendar day of the month.

Using 6/1/2011 to 6/30/2011 to start with. How does one use a formula to account for the number of days in a month, when adding the logic of 1-month? In other words, how do I end up with 7/31/2011, and not 7/30/2011?


Thanks,
SHD

 

[Scott Huish]

Load the Analysis Toolpak.

=EOMONTH(A1,1) where A1 holds your original date.

Format as date.

Excel Workbook
	A	B
1	6/30/2011	7/31/2011
Sheet1

 

[shdawson]

WOW, that is cool!


Thanks,
SHD

 

[shdawson]

Further question, please.

This formula:

=EOMONTH(J$3,0)

...pushes the date to the end of the month, sure enough.



However, it pushes...
6/1/11 12:00:00 AM

...to
6/30/11 12:00:00 AM


What I really need is a formula to also push it to:
6/30/11 11:59 PM



That will give me all of the time of the day of the last day of the month.

How can your formula be edited to accomplish this, please?


Thanks,
SHD

 

[Scott Huish]

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

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

 

[shdawson]

Thank you very much!

The subtraction of the 1-second thing, I did not know how to do that. Wow, that will help lots of other calc's I have going.

Very much appreciated. Have a great weekend.


Kindly,
SHD