Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Formula not returning the correct date

Topic Labels: Formulas
Solved
Jump to Solution
332 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Solution

Accepted Solutions

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).

CleanShot 2021-02-08 at 15.22.21@2x

See Solution in Thread

2 Replies 2

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).

CleanShot 2021-02-08 at 15.22.21@2x

That did the trick. Thanks very much!!!