Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Find the value of the next month based on today.

John Bandy
edited 12/09/19 in Archived 2015 Posts

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?

Tags:

Comments

  • Travis
    Travis Employee

    Hi John! This was a fun (and new) one for me! Cool 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! 

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

  • 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
    Kennedy Stomps Employee
    edited 06/01/16

    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.

This discussion has been closed.