I have a base that I’m using to track projects, which have a start date and an end date. Sometimes those projects get modified for a variety of reasons with one of those reasons sometimes being that the end date gets extended. We track all modifications in a separate modifications table that gets linked to the original project. We want to be able to dynamically display the new project end date by referencing all mods and returning the latest date. To do that, I have set up a Rollup Field linked to the Modifications table that returns the maximum value: MAX(values)

But if there are no modifications, that Rollup Field will be empty, which won’t work.

So, after reading the forums, I created a formula field date with the following logic:

IF({Rollup Project End Date}>{End Date}, {Rollup Project End Date},{End Date})

It appears to work at first, but upon closer inspection, the date getting returned in that formula field is one day earlier than it should be. I need it to be exact because people are using this to track due dates. What could I be doing wrong?

To test it out, I created a new project that had an end date of 1/30/21. I then added a modification for that project with a new end date of 1/30/22. The rollup date appears as 1/30/22, but the formula field date appears as 1/29/22. I added a second mod extending the project to 1/30/23 and the formula field date appears as 1/29/23.

It is also subtracting a day from any project end dates that don’t have a modification.