# Result from formula to get end date from date range is wrong **URL:** \ **Category:** Bugs & Issues\ **Created:** [August 13, 2021, 9:11pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917 "2021-08-13T21:11:44Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Dimitri\_S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/dimitri_s/32/1683_2.png) [@Dimitri\_S](https://community.fibery.io/u/Dimitri_S)\ **Post date:** [August 13, 2021, 9:11pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/1 "2021-08-13T21:11:44Z")
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](https://community.fibery.io/t/converting-an-end-date-to-text/1302) 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: ---
**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\ **Post date:** [August 13, 2021, 10:10pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/2 "2021-08-13T22:10:41Z")
Maybe related: [Formula adds one day to Max dates](https://community.fibery.io/t/formula-adds-one-day-to-max-dates/1348) ---
**Author:** ![Dimitri\_S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/dimitri_s/32/1683_2.png) [@Dimitri\_S](https://community.fibery.io/u/Dimitri_S)\ **Post date:** [August 16, 2021, 7:47pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/3 "2021-08-16T19:47:18Z")
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. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 16, 2021, 9:16pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/4 "2021-08-16T21:16:37Z")
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](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/f/f032bb6becd8eefe1b69ec4d23cf9b2ab6b047cc.png) ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/f/fbaa6bc50a78e5eb8012641bbdf48667549e69cb.png) If a user were interested in calculating the duration of that task, he/she would be advised to use the following formula: ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/f/fd1b76a0ce335807967b48efa4fddb559b6a454c.png) 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](https://community.fibery.io/t/formula-adds-one-day-to-max-dates/1348) and the issue I reported a while back [here](https://community.fibery.io/t/converting-an-end-date-to-text/1302) 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… ---
**Author:** ![Dimitri\_S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/dimitri_s/32/1683_2.png) [@Dimitri\_S](https://community.fibery.io/u/Dimitri_S)\ **Post date:** [August 16, 2021, 10:13pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/5 "2021-08-16T22:13:18Z")
> [@Chr1sG](#): > > the value of DateRange.End _as stored internally_ (= date shown in UI +1 day). 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. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 16, 2021, 10:21pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/6 "2021-08-16T22:21:17Z")
Yep, there’s no easy fix, and any solution is likely to break backwards compatibility for some people in some places ☹ I just figured it was fair to be open about things. ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [September 27, 2021, 10:40am UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/8 "2021-09-27T10:40:02Z")
Good news 🤩 > [@CHANGELOG: Sep 27 / Milestones on Timeline, Date Ranges in Formulas, Filter Linked Entities in Rules](https://community.fibery.io/t/changelog-sep-27-milestones-on-timeline-date-ranges-in-formulas-filter-linked-entities-in-rules/2061): > > Milestones on a Timeline [https://community.fibery.io/t/in-dev-milestones-on-a-timeline/671](https://community.fibery.io/t/in-dev-milestones-on-a-timeline/671) Add crucial dates as milestones to any Timeline View: [milestones-on-timeline-fictional] Releases, conferences, product launches — everything preceded by firefire_engine time is a good candidate to visualize as a milestone. In a classic Fibery fashion, there are no hardcodes: pick any Type(s), any common Field, and use the usual filters to keep truly relevant events only. Date Ranges in Formulas a… ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [September 27, 2021, 10:42am UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/9 "2021-09-27T10:42:24Z")
> [@Dimitri\_S](#): > > There would clearly be some data migration necessary FYI: if you had used `.End` in a formula previously, we will swap it over to `.End(false)` so that everything should still work as you set it up. ---
**Author:** ![Dimitri\_S](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/dimitri_s/32/1683_2.png) [@Dimitri\_S](https://community.fibery.io/u/Dimitri_S)\ **Post date:** [September 27, 2021, 4:23pm UTC](https://community.fibery.io/t/result-from-formula-to-get-end-date-from-date-range-is-wrong/1917/10 "2021-09-27T16:23:35Z")
Thanks! 🙌 To sum up from the changelog: > [@CHANGELOG: Sep 27 / Milestones on Timeline, Date Ranges in Formulas, Filter Linked Entities in Rules](https://community.fibery.io/t/changelog-sep-27-milestones-on-timeline-date-ranges-in-formulas-filter-linked-entities-in-rules/2061/1): > > Here are the TLDR updates: > > 1. New `DateRange(startDate, endDate)` and `DateTimeRange(startDateTime, endDateTime)` functions. > 2. `[Date Range Field].End()` syntax instead of `[Date Range Field].End` . > > @Chr1sG has prepared a [guide for the power users](https://help.fibery.io/en/articles/5598869-date-ranges-in-formulas).