Add 3mth date increment to 'date completed'

atotsidis

Hello, i'm hoping someone can assist me with a formula that i'm entering which returns an invalid error. I have scoured the help forum for assistance, but not had any luck replicating the formulas in my sheet.

What i want to do is automatically generate a date which is 3mths after the date entered in the 'date completed' column.

date 3mth.JPG

Paul Newcome

@atotsidis That error comes from trying to output a date into a non-date type column. Try changing the column type to a date type.

DKazatsky2

Hi @atotsidis,

My initial thought is for you to use a helper column to determine the month 3 months later and use that to determine the new date. Here is a screenshot of the results - in real use you would probably hide the helper column.

Helper column formula: =IF(MONTH([Date Completed]@row) + 3 > 12, MONTH([Date Completed]@row) + 3 - 12, MONTH([Date Completed]@row) + 3)

Post Completion column formula: =DATE(YEAR([Date Completed]@row), [3mths later (helper)]@row, DAY([Date Completed]@row))

There may a more elegant solution, but this is what I came up with for now and it seems to be working.

Hope this helps,

Dave

atotsidis

Thanks so much Dave. I haven't explored this helper column route before. Help me understand, exactly what values do i need to enter into the helper column? I see that the center column highlighted in blue does provide the accurate dates 3 mths post completion, but i cannot make sense of the values in the helper column. can you elaborate here?

Paul Newcome

You shouldn't need a helper column. Try this:

=IFERROR(DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 3, DAY([Date Completed]@row)), DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row) - 9, DAY([Date Completed]@row)))

Nick Korna

You can skip the helper column:

=IF(MONTH([Date Completed]@row) + 3 <= 12, (DATE(YEAR([Date Completed]@row), MONTH([Date Completed]@row) + 3, DAY([Date Completed]@row))), (DATE(YEAR([Date Completed]@row) + 1, MONTH([Date Completed]@row) - 9, DAY([Date Completed]@row))))

As you can see from the example, if the month you're using is shorter than the month 3 months from the date it will move the date appropriately.

Or if you're not pick about it being exactly 3 months but 90 days, then this will work:

=[Date Completed]@row + 90

Pick whichever option appeals! 😊

atotsidis

@Paul Newcome unfortunately the formula didn't work. I copied and pasted it but it returns an invalid message.

ss.JPG

ss1.JPG

atotsidis

@Nick Korna that worked! thank you!

Paul Newcome

@atotsidis That error comes from trying to output a date into a non-date type column. Try changing the column type to a date type.