Date incrementing column formula works fine except in two cases.
Hi there,
I am using a formula to establish future dates (Scheduled Pull-Out Date) based on a starting date (Packaging Date) and adding a number of months according to the 'Time Point' column. The formula seems to work fine until I noticed that two entries (in purple) are not following the rule; they are supposed to add 9 months to the packaging date but the future date is overrun by more than a year. I had to implement IFERROR modifier as sometimes the future dates would not switch to the next year or return an error.
Formula as below (apologies for the block of text):
=IFERROR(IF([TIME POINT]@row = "T0", [PACKAGING DATE]@row , IF([TIME POINT]@row = "3M", DATE(YEAR([PACKAGING DATE]@row ), MONTH([PACKAGING DATE]@row ) + 3, DAY([PACKAGING DATE]@row )), IF([TIME POINT]@row = "6M", DATE(YEAR([PACKAGING DATE]@row ), MONTH([PACKAGING DATE]@row ) + 6, DAY([PACKAGING DATE]@row )), IF([TIME POINT]@row = "9M", DATE(YEAR([PACKAGING DATE]@row ), MONTH([PACKAGING DATE]@row ) + 9, DAY([PACKAGING DATE]@row )), IF([TIME POINT]@row = "12M", DATE(YEAR([PACKAGING DATE]@row ) + 1, MONTH([PACKAGING DATE]@row ), DAY([PACKAGING DATE]@row )), ""))))), IF([TIME POINT]@row = "T0", [PACKAGING DATE]@row , IF([TIME POINT]@row = "3M", DATE(YEAR([PACKAGING DATE]@row ) + 1, 2, DAY([PACKAGING DATE]@row )), IF([TIME POINT]@row = "6M", DATE(YEAR([PACKAGING DATE]@row ) + 1, 5, DAY([PACKAGING DATE]@row )), IF([TIME POINT]@row = "9M", DATE(YEAR([PACKAGING DATE]@row ) + 1, 8, DAY([PACKAGING DATE]@row )), IF([TIME POINT]@row = "12M", DATE(YEAR([PACKAGING DATE]@row ) + 1, MONTH([PACKAGING DATE]@row ), DAY([PACKAGING DATE]@row )), ""))))))
The formula seems to work with other dates for 9M. In addition, if there are any suggestions on how the formula syntax can be shortened, it would be appreciated.
Thanks and regards,
Justin
Best Answer
-
This will handle negative and positive numbers of months without having to hard-code the individual variables:
=IFERROR(IFERROR(DATE(YEAR([PACKAGING DATE]@row ) + ROUNDDOWN((MONTH([PACKAGING DATE]@row ) + [Number of Months]@row ) / 12, 0) + IF(IF(MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12) = 0, 12, MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12)) = 12, -1) - IF(AND(ABS([Number of Months]@row ) - MONTH([PACKAGING DATE]@row ) <> 12, [Number of Months]@row Β < 0, ABS([Number of Months]@row ) > MONTH([PACKAGING DATE]@row )), 1, 0), IF(MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12) = 0, 12, MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12)), DAY([PACKAGING DATE]@row )), DATE(IF(MONTH([PACKAGING DATE]@row ) - ABS([Number of Months]@row ) < 1, YEAR([PACKAGING DATE]@row ) - 1, YEAR([PACKAGING DATE]@row )), IF(MONTH([PACKAGING DATE]@row ) - ABS([Number of Months]@row ) < 1, MONTH([PACKAGING DATE]@row ) + (12 - ABS([Number of Months]@row )), MONTH([PACKAGING DATE]@row ) - ABS([Number of Months]@row )), DAY([PACKAGING DATE]@row ))), "")
[Number of Months] is a helper column with this formula to extract just the number from the [TIME POINT] column:
=VALUE(SUBSTITUTE([TIME POINT]@row, "M", ""))
Answers
-
This will handle negative and positive numbers of months without having to hard-code the individual variables:
=IFERROR(IFERROR(DATE(YEAR([PACKAGING DATE]@row ) + ROUNDDOWN((MONTH([PACKAGING DATE]@row ) + [Number of Months]@row ) / 12, 0) + IF(IF(MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12) = 0, 12, MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12)) = 12, -1) - IF(AND(ABS([Number of Months]@row ) - MONTH([PACKAGING DATE]@row ) <> 12, [Number of Months]@row Β < 0, ABS([Number of Months]@row ) > MONTH([PACKAGING DATE]@row )), 1, 0), IF(MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12) = 0, 12, MOD(MONTH([PACKAGING DATE]@row ) + [Number of Months]@row , 12)), DAY([PACKAGING DATE]@row )), DATE(IF(MONTH([PACKAGING DATE]@row ) - ABS([Number of Months]@row ) < 1, YEAR([PACKAGING DATE]@row ) - 1, YEAR([PACKAGING DATE]@row )), IF(MONTH([PACKAGING DATE]@row ) - ABS([Number of Months]@row ) < 1, MONTH([PACKAGING DATE]@row ) + (12 - ABS([Number of Months]@row )), MONTH([PACKAGING DATE]@row ) - ABS([Number of Months]@row )), DAY([PACKAGING DATE]@row ))), "")
[Number of Months] is a helper column with this formula to extract just the number from the [TIME POINT] column:
=VALUE(SUBSTITUTE([TIME POINT]@row, "M", ""))
-
Many thanks, this seems to have worked!
Justin
Help Article Resources
Categories
Check out the Formula Handbook template!