Date Formula

Pamela Wagner
Pamela Wagner ✭✭✭✭✭✭
in Formulas and Functions

I am creating a sheet to be used as a template for tracking tasks per contracts. For invoices, I would like to have a section that will have Invoice 1, 2, -12 but I would like to just be able to input one date and then have the other dates flow in once the first date is entered. So if Invoice 1 gets a date due of 01/15/20 then the other 11 will flow once that 01/15/20 is entered and we will see 02/15/20, 03/15, 20, etc - 12/15/20. In excel I would just: =b17+30 and then copy it down but that does not see to be working.

· Share on FacebookShare on Twitter

Best Answer

Answers

  • Joachim Mund
    Joachim Mund ✭✭✭✭✭✭

    Hi Pamela,

    I hope this helps:

    Date Formula Invoice 2...12: =DATE(YEAR(Date1); MONTH(Date1) + 1; DAY(Date1))

    image.png


    · Share on FacebookShare on Twitter
  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    Thanks @Joachim Mund! However, I'm not really sure how to tie that to the original cell and what the string means.

    If the original cell is called "Due Date" and the first date is 01 June 2020 and the subsequent dates are + 30, what does the string look like? Sorry but Smartsheet formulas are new to me!

    · Share on FacebookShare on Twitter
  • Joachim Mund
    Joachim Mund ✭✭✭✭✭✭

    row2 formula: =DATE(YEAR([Due Date]1); MONTH([Due Date]1) + 1; DAY([Due Date]1)) and pull this formula down to the other rows

    "MONTH([Due Date]1) + 1" means +1 month not only +30 days

    · Share on FacebookShare on Twitter
  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    Ugh. It's saying unparseable. Here is the formula (and I also tried it Month Day Year just in case):

    =DATE(YEAR([Due Date]14); MONTH([Due Date]14) + 1; DAY([Due Date]14))

    Pretty sure all of the spaces are correct compared to yours.

    · Share on FacebookShare on Twitter
  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    @Joachim Mund it worked!!! Well, at first it didn't as it came back with a #INVALID COLUMN VALUE as I thought when I'm using a value that the column should be Text/Number and not Date but once I turned it to Date it worked. Thanks much!!

    · Share on FacebookShare on Twitter
  • Joachim Mund
    Joachim Mund ✭✭✭✭✭✭

    😉

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You may want to amend this solution slightly... This works if the first invoice is in the month of January, but what if the first invoice is in a later month? Eventually your formula will be trying to output a month number of 13 which will throw an error.

    To account for this, you will want to use an IFERROR statement to generate a month number of 1 for the following year then pick back up from there with the original formula.

    =IFERROR(current date formula pointing at row 1, DATE(YEAR([Due Date]1 + 1, 1, DAY([Due Date]1))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Pamela Wagner
    Pamela Wagner ✭✭✭✭✭✭

    Hi @Paul Newcome! You are correct that I did get an error. Good example is the first use of this is for monthly invoices which means that I could have a minimum of 12 which will go into a new year. I'm sure that I'm not taking your instructions correctly as I'm getting an INCORRECT ARGUMENT SET message back. Here is my formula:

    =IFERROR(DATE(YEAR([Due Date]30 + 1, 1, MONTH([Due Date]30) + 1, DAY([Due Date]30)))

    I used 12/25/2020 as the due date in row 30 so that it would flow to 01/01/2021.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!