Result from formula to get end date from date range is wrong

Seems like this might be related to this old bug reported by @Chr1sG not sure if it was ever fixed: Converting an End date to text

So if I have a date range field called Invoice Period and then I create a formula field with the formula of [Invoice Period].End the result isn’t actually the end date but the end date + 1 day.

So if Invoice Period is “Jan 1, 2022 → Jan 9, 2022” the [Invoice Period].End formula incorrectly produces a Jan 10, 2022 result.

Here is a demonstration of the bug:

Maybe related: Formula adds one day to Max dates

It also looks like when creating an entity via the API with an start/end date type the end date incorrectly set to the day before. I can confirm this because I submitted the same exact date in the same request and the regular date is set correctly but the end date is incorrect.

With this many problems I don’t think the start / date option for the date field is usable.

I thought it would be sensible to write something to explain the underlying cause of this issue, so that those who are experiencing it can understand it and find ways to avoid the problems described here (and in the other related issues).

This isn’t to say that the behaviour is to be expected, or that the problems do not need to be fixed, but I’m just giving a bit of context so that it doesn’t look like Fibery is irretrievably broken!

The date range consists of two dates, let’s call them DateRange.Start and DateRange.End, and it’s often used to represent an activity that is spread over a period of days (or weeks/months/years).

Imagine a task that starts on the 14th August and finishes on the 15th August, which is shown in the UI as
image

image

If a user were interested in calculating the duration of that task, he/she would be advised to use the following formula:
image
which will give 2 as the result.
For most people, this is probably the answer that would be expected. So far so good :slight_smile:

But in order to get that answer, a decision was taken to store the DateRange.End internally as a day later than the value shown in the UI. This is almost akin to deciding that the task actually ends at midnight on the evening of the 15th August (i.e. 1 second after 15/08/2021 23:59:59) which is 00:00 on the 16th August.

The result of this decision is that when using formulas and automations, the user is operating on the value of DateRange.End as stored internally (= date shown in UI +1 day).

And the side effect of that decision is that using DateRange.End may give counter-intuitive behaviour in other contexts.

In the examples above from @Dimitri_S (evaluating DateRange.End in a formula, or using automation/API to set the value of DateRange.End) what ends up being shown in the UI will be wrong.

Similarly, the experience of @Haslien here and the issue I reported a while back here stem from the same underlying design decision.

The long-term fix for this is still to be determined, and unfortunately there’s no quick/simple solution. I’d be happy to talk to people about the current ideas for possible fixes, but since they’re a bit technical, I shan’t post them here.

In the mean time, I thought there was value in posting this info, so that anyone who finds themselves using DateRange.End in a formula/automation can understand the inner workings and can make a decision to apply a -1 day correction if appropriate.

Feel free to add commetnts/questions…

2 Likes

I think the fix would be directed at undoing this. There would clearly be some data migration necessary or maybe adding custom handling to DateRange.End - DateRange.Start for legacy support.

But having the input of 08/14 - 08/15 and actually storing it as 08/14 - 08/16 only to make the DateRange.End - DateRange.Start formula work makes the date range feature fundamentally broken.

Suggesting for users to use the “-1 day correction” (while a good intention) is just piling bad on top of bad and would make any chance of properly fixing this in the future all but impossible.

Yep, there’s no easy fix, and any solution is likely to break backwards compatibility for some people in some places :frowning:
I just figured it was fair to be open about things.

1 Like