Date Formula

Pamela Wagner

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.

Joachim Mund

change ; to , like:

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

I have the german country setting

Joachim Mund

Hi Pamela,

I hope this helps:

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

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!

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

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.

Joachim Mund

change ; to , like:

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

I have the german country setting

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!!

Joachim Mund

😉

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))

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.