Formula not returning the correct date

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.

Hi @Katie_Kline,

On the Formula field that is returning the date as 1-day behind (and only on that field), select the “Formatting” tab in the field options menu and enable the “Use the same time zone…” option. This usually fixes the problem by forcing the Formula field to use the same time-zone as the originating Date field (even though the language in that option doesn’t seem like it would).

That did the trick. Thanks very much!!!

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.