Some cells not populating with column formula

Michaela Kamenska

Hello all,

I've got a column formula that automatically determines start date based on a different date. It appears though that some cells are not populating (see snapshot) and I wondered if anyone can advise why it might be? The Start-Up duration column has a column formula "=3" for 3 months and Start-Up start date should be calculated as 3 months before FPI/Start-Up End. It appears that it may have issues going back a year and I'm not sure how to sort it out.

The formula is as follows:

=IFERROR(DATE(YEAR([FPI/Start-Up End]@row) + ROUNDDOWN((MONTH([FPI/Start-Up End]@row) - [Start-Up duration (months)]@row) / 12, 0) + IF(IF(MOD(MONTH([FPI/Start-Up End]@row) - [Start-Up duration (months)]@row, 12) = 0, 12, MOD(MONTH([FPI/Start-Up End]@row) - [Start-Up duration (months)]@row, 12)) = 12, +1), IF(MOD(MONTH([FPI/Start-Up End]@row) - [Start-Up duration (months)]@row, 12) = 0, 12, MOD(MONTH([FPI/Start-Up End]@row) - [Start-Up duration (months)]@row, 12)), DAY([FPI/Start-Up End]@row)) + 1, 0)

Paul Newcome

If it is ALWAYS going to be 3 months, then you can use this instead:

=IFERROR(DATE(YEAR([\[FPI/Start-Up End\]]@row), MONTH([\[FPI/Start-Up End\]]@row) - 3, DAY([\[FPI/Start-Up End\]]@row)), DATE(YEAR([\[FPI/Start-Up End\]]@row) - 1, MONTH([\[FPI/Start-Up End\]]@row) + 9, DAY([\[FPI/Start-Up End\]]@row)))

Neil Watson

Its does appear to be related to the dates from the previous year, this post might help:

https://community.smartsheet.com/discussion/comment/254561#Comment_254561

Paul Newcome

If it is ALWAYS going to be 3 months, then you can use this instead:

=IFERROR(DATE(YEAR([\[FPI/Start-Up End\]]@row), MONTH([\[FPI/Start-Up End\]]@row) - 3, DAY([\[FPI/Start-Up End\]]@row)), DATE(YEAR([\[FPI/Start-Up End\]]@row) - 1, MONTH([\[FPI/Start-Up End\]]@row) + 9, DAY([\[FPI/Start-Up End\]]@row)))

Michaela Kamenska

Thanks @Paul Newcome, but unfortunately it shows me as unparseable. Can you help?

Michaela Kamenska

@Paul Newcome I removed the slashes and extra square brackets and the formula worked, thanks! Not sure if they serve any purpose or if it was just a mistake?

Paul Newcome

My apologies. That was my fat fingers getting in the way. Glad you were able to get it working.