• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating 1st of month following a date

B

bandit1978

New Member
I need to calculate the 1st of the month following a date, but not if the date is already the 1st of the month. I was using the formula =DATE(YEAR(B5),MONTH(B5)+1,1)to calculate the next month, but this formula doesn't work when the date is already the first of the month and I don't want the next month then. For example if the date is 8/2/2012, I want 9/1/2012 displayed, but if the date is 8/1/2012, I do not want it to calculate the next month. Any help would be awesome!

Thanks!
 
This should do it:

=EOMONTH(A2,0-(DAY(A2)=1))+1


Or, if you want to stick with your previous format:

=DATE(YEAR(B5),MONTH(B5)+(DAY(B5)<>1),1)
 
If the case only for 1st day, just -1 in MONTH field.


=DATE(YEAR(B5),MONTH(B5-1)+1,1)


with EOMONTH,


=EOMONTH(B5-1,0)+1
 
You must log in or register to reply here.
Back
Top