Formula to calculate 30 days before

alys

Hi, I have a column with a Partner Due Date (manually entered), and a column with an Internal Due Date (column formula). I would like for the Internal Due Date to be 30 days before the Partner Due Date and am using this formula:

=DATE(YEAR([Partner Due Date]@row), MONTH([Partner Due Date]@row) - 1, DAY([Partner Due Date]@row))

For any Partner Due Dates that are January, I am getting an #INVALID VALUE error. Any suggestions for how to fix this formula so that January Internal Due Dates are December? Thank you!

Kelly P.

@alys If you literally want the internal due date to be 30 days prior to the partner due date, then you can just use:

=[Partner Due Date]@row - 30

However, it looks like what you really want is the same day of the month, but one month prior. If so, try this:

=IFERROR(IF(MONTH([Partner Due Date]@row) > 1, DATE(YEAR([Partner Due Date]@row), MONTH([Partner Due Date]@row) - 1, DAY([Partner Due Date]@row)), IF(MONTH([Partner Due Date]@row) = 1, DATE(YEAR([Partner Due Date]@row) - 1, MONTH([Partner Due Date]@row) + 11, DAY([Partner Due Date]@row)))), "")

The IFERROR is there just in case there are any blank partner due dates.

Kelly P.

@alys If you literally want the internal due date to be 30 days prior to the partner due date, then you can just use:

=[Partner Due Date]@row - 30

However, it looks like what you really want is the same day of the month, but one month prior. If so, try this:

=IFERROR(IF(MONTH([Partner Due Date]@row) > 1, DATE(YEAR([Partner Due Date]@row), MONTH([Partner Due Date]@row) - 1, DAY([Partner Due Date]@row)), IF(MONTH([Partner Due Date]@row) = 1, DATE(YEAR([Partner Due Date]@row) - 1, MONTH([Partner Due Date]@row) + 11, DAY([Partner Due Date]@row)))), "")

The IFERROR is there just in case there are any blank partner due dates.

alys

@Kelly P. thank you!

Kelly P.

@alys Happy to help! 😊