Auto Dating Formulas for Next Month

Samantha S.

I have a formula that was built to generate as the 1st day of the next month following a Fail status. I had this working, but then we changed our terminology from Fail to Development and now it is just leaving my start date blank and I'm not sure why. I even tried recreating from scratch. Any thoughts?

Austin Smith

@Sam H.

Try This

=IFERROR(IF(MONTH([Attempt #3 Date]@row) < 12, IF([Attempt #3 Status]@row = "Development", DATE(YEAR([Attempt #3 Date]@row), MONTH([Attempt #3 Date]@row) + 1, 1), ""), ""), "")

Samantha S.

This would generate the wrong date for any Attempts occurring in December though.

Austin Smith

@Sam H.

Sorry, try this.

=IFERROR(IF(MONTH([Attempt #3 Date]@row) < 12, IF([Attempt #3 Status]@row = "Development", DATE(YEAR([Attempt #3 Date]@row), MONTH([Attempt #3 Date]@row) + 1, 1), ""), IF(MONTH([Attempt #3 Date]@row) = 12, IF([Attempt #3 Status]@row = "Development", DATE(YEAR([Attempt #3 Date]@row) + 1, 1, 1), ""), "")), "")

Samantha S.

It's still coming up blank:

Samantha S.

I figured it out. There was a bug where my date column wasn't converting to a short date correctly on another sheet that feeds to the Attempt Date column on this sheet. I switched it to text and back to Date and now my formula works again. Thanks for your help though!