Excel formula - 1st of month following 30 days 1

[LLawson]

I am trying to write a formula in excel that will convert a date to the 1st of the month following 30 days. Can anyone help me?

 

[ETID]

....following 30 days????

Can you elaborate?

 

[LLawson]

In cell A1, I have the date 8/5/1991. I need to convert the date to the 1st of the month following 30 days.

1. Add 30 days
I added 30 days to cell A1 and get 9/5/1991.

2. 1st of next month after 30 days
But I need to some how round the date up to the next 1st of the month to show 10/1/1991 as my start date.

Here is another example, suppose my date is 7/1/1991. If I add 30 days, it gives me 7/31/1991. The next 1st month is 8/1/1991, which is what I want to show after the initial 30 days.

 

[PeterMoran]

Hi LLawson,

With the original date in B18, try out this formula:

=DATE(IF(AND(MONTH(B18)=12,DAY(B18)<>1),YEAR(B18)+1,YEAR(B18)),MONTH(B18+30)+1,1)

I think it will work in accordance with your specifications.

Note these two examples:

1/1/03 will give 2/1/03 according to your specs, as will the first day of any 31 day month.

because February has only 28 days, 1/31/03 will give 4/1/03

I suggest you should test out the formula with some more examples until you are happy that it does what you want.

Good Luck!
Peter Moran

 

[KenWright]

With your date in A1, try the following:-

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

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlhelp]

=EOMONTH(A1,1)+1
would be the simplest. Only problem will be that it will not give you the right answer for Jan 30th and/or 31st.

You also need Analysis Toolpak installed under Tools, Addin; which as simple as putting the checkmark in the box and clicking OK.

http://www.geniusmedia.ca

 

[KenWright]

Using EOMONTH will give a number of incorrect responses due to it adding a 'month' as opposed to 30 days, eg:-

01/01/2003
30/01/2003
31/01/2003
01/03/2003
01/05/2003
01/07/2003
01/08/2003
01/10/2003
01/12/2003
01/01/2004

Each will give an answer 1 month later than it should be, with the exceptions (as you noted) of Jan 30/31 in which case it actually returns a month less than it should (Based on 2003 data).

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------