Add a date (first of month)

lmarantos

Hi there! We have a smartsheet that is for program enrollment. The program start date is the first day of the following month after the enrollee's start date (so, if they start on 11.15 in their office, their program start date is 12.1). I originally used:  

=DATE(YEAR([Net Add Start Date]@row), MONTH([Net Add Start Date]@row) + 1, 1)

as my formula and it has worked just fine until now. Those who are starting in December and therefore have a program start date in January are now showing #invalid value errors, seemingly because of the year change. I have researched the IFERROR formulas and came up with this:

=IFERROR(DATE(YEAR([Net Add Start Date]@row), MONTH([Net Add Start Date]@row) + 1, 1), DATE(YEAR([Net Add Start Date]@row) + 1, MONTH([Net Add Start Date]@row) – 11, 1)))

Which does show the correct current information like I previously had, but still doesn't fix the year change issue and still shows an error. I am stumped!

Paul Newcome

I imagine the error is most likely #UNPARSEABLE. It looks like you have one too many closing parenthesis there on the end which would cause that error, but...

If it is only going forward to the next month then you can use something like this:

=IFERROR(DATE(YEAR([Net Add Start Date]@row), MONTH([Net Add Start Date]@row) + 1, 1), DATE(YEAR([Net Add Start Date]@row) + 1, 1, 1))

Basically if it errors out then output Jan 1 of the following year. Since the only month that should cause errors is December and we are only going forward one month, we do not need to worry about trying to subtract months or anything like that. Just add 1 to the year and then set it as Jan 1.

lmarantos

Thank you so much!!! That makes WAY more sense!! I appreciate your help :)

Paul Newcome

Happy to help. 👍️

Nicole Arnold

Hello @Paul Newcome! I used this formula for a sheet I am working on, and I need to add something to the formula so that "#INVALID DATA TYPE" doesn't appear if we have a row with some data in it but we have not entered the date that the formula is built off. We are using this to automate a message with an EOI link for employees who have requested supplemental life insurance above the guarantee issue amount. There will be times when the employee volume is not above GI and the spouse will be, and vice versa. We have an automation built to notify the employee when the EOI has been approved, and since we don't want to send two separate emails (one for the employee volume, one for the spouse volume), the "#INVALID DATA TYPE" error does show in the email if one of the triggering date fields is blank. I've attached an image as to what the sheet looks like. Are you able to add to your original formula above to prevent this error message? Thanks for your help!

Paul Newcome

@Nicole Arnold You only need an IFERROR statement.

=IFERROR(original_formula, "")

Nicole Arnold

@Paul Newcome I used the exact IFERROR formula you provided for the original post/question. However, when there is not a date entered in EE Life - Eff Date for Approved Amt, the #INVALID DATA TYPE error displays. There will not always be a date in the EE Life - Eff Date for Approved Amt column, so I am looking for whatever it is I need to add to your original formula to prevent the error from displaying. Following is my current formula:

=IFERROR(DATE(YEAR([EE Life - Eff Date for Approved Amt]@row), MONTH([EE Life - Eff Date for Approved Amt]@row) + 1, 1), DATE(YEAR([EE Life - Eff Date for Approved Amt]@row) + 1, 1, 1))

Are you able to assist? Thank you!

Paul Newcome

@Nicole Arnold Right. You would wrap the whole thing in another IFERROR.

Nicole Arnold

@Paul Newcome I just did that and now I receive the #INCORRRECT ARGUMENT error. This is the new formula:

=IFERROR(IFERROR(DATE(YEAR([EE Life - Eff Date for Approved Amt]@row), MONTH([EE Life - Eff Date for Approved Amt]@row) + 1, 1), DATE(YEAR([EE Life - Eff Date for Approved Amt]@row) + 1, 1, 1)))

KPH

When you add the second IFERROR to the start of the formula, you also need to include something at the end to tell it what to do if there is an error. If you want to put nothing in the cell if there is an error then add this part in bold.

=IFERROR(IFERROR(DATE(YEAR([EE Life - Eff Date for Approved Amt]@row), MONTH([EE Life - Eff Date for Approved Amt]@row) + 1, 1), DATE(YEAR([EE Life - Eff Date for Approved Amt]@row) + 1, 1, 1)),"")

Paul Newcome

@Nicole Arnold You forgot the "value if error" portion at the end.

=IFERROR(original_formula, "")