Find the value of the next month based on today.

John Bandy

How do I add 1 month to today? I don't want to add 30 days or anything, I just want to get to the next month and I have to be able to cross years. so if today is 2/1/2015 I want to find March is next; if today is 12/31/2015 I want to find Jan is next. Do I need a lookup or is there a date function that would get me this?

Travis

Hi John! This was a fun (and new) one for me!  I built a formula that will show the date of the first day of the next month of a selected date cell. 

 

Here is the formula:

 

=IF(ISDATE(Date1), IF(MONTH(Date1) = 12, DATE((YEAR(Date1) + 1), 1, 1), DATE(YEAR(Date1), (MONTH(Date1) + 1), 1)))

 

First, it checks to make sure the date in Date1 is a date which will make sure it does not produce an error if the cell is blank. (IF(ISDATE(Date1))

 

Next, it checks if the date is in December. If it is, then it will show the first month of the year (January) and will add one to the year. (IF(MONTH(Date1) = 12, DATE((YEAR(Date1) + 1), 1, 1))

 

Finally using the DATE function, it will take the year from the selected date cell, then it will take the month (plus 1), and finally, it will show the first day of the month. (DATE(YEAR(Date1), (MONTH(Date1) + 1), 1))

 

You will need to update the Date1 cell reference to match your sheet contents. 

 

If you are not referencing a date cell but want to always use *todays* date as the referenced date, then just exchange the cell references with TODAY():

 

=IF(MONTH(TODAY()) = 12, DATE((YEAR(TODAY()) + 1), 1, 1), DATE(YEAR(TODAY()), (MONTH(TODAY()) + 1), 1))

 

 

Let me know if you any questions on this! 

John Bandy

Excellent, that is what I was looking for. Thank you!

LouSnz

Hello, I am looking for something similar, 

I have a checkbox column that is checking if the 'created' date is the current month.

I then have a start date column that I want to add the date of the 1st of the month (referencing todays month), if the checkbox column is empty (ie if the line was not created this month).

 

Would something similar to the above work for me?

 

 

Kennedy Stomps

Hi LouSnz-- it looks like Craig may have come up with a solution for your case specifically here. If you have any questions on implementing that in your sheet, let me know and I'll be happy to assist.