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
If a user were interested in calculating the duration of that task, he/she would be advised to use the following formula:
which will give 2 as the result.
For most people, this is probably the answer that would be expected. So far so good
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…