In this article, we'll explain how date ranges are handled in Fibery, and how to make use of them in formulas.
What is a date range?
In Fibery, a date range is a date field, where the option to include start and end dates has been chosen. A date-time range is the same but where the option to include time has also been chosen:
Ranges appear in the entity view as 'start → end' and on the timeline as bars:
But really, what is a date range?!
Any range (date range or date-time range) is basically an interval of time from the start to the end, so for example, 'Aug 20, 2021 → Sep 7, 2021' covers all the days in that range: Aug 20, Aug 21, Aug, 22, … Sep 6, Sep 7.
We store the data internally as two values (start and end) but for a number of technical reasons (feel free to discuss the mathematics of intervals with our developers 🥱😛) the end value stored is actually greater than what is shown on the UI by +1 🤨
One way to think about this is that the interval of time 'Aug 20 → Sep 7' starts at the very beginning of the day on August 20th and ends at the very end of the day on September 7th ... and the end of September 7th is equivalent to the start of the next day, Sep 8th 🤔
Similarly, a date-time range is actually stored internally as two values (start and end) where the end date-time value is stored internally as one second later than what is shown on the UI.
Too confusing! What do I need to know?
Most of the time, this technical decision makes no difference to your experience of using Fibery, and you can rest easy 😴 but if you start using date ranges (or date-time ranges) in formulas, things might not behave completely intuitively for you.
In the example date range above (20 Aug → 7 Sep) if you want to get the start date, as shown on the UI, you should use the following formula:
If you want the end date, as shown on the UI, you should use this formula:
However, if you are planning on calculating the duration of a task (in days) then you will probably want to use the following formula:
ToDays([Date Range].End(false) - [Date Range].Start())
This corresponds to the total duration of the interval, and you will get an answer of 19 (12 days in August + 7 days in September).
This is because
.End(false) returns the end date as stored internally
( = value shown on UI + 1 )
What about date-time ranges?
It's basically all the same for date-time-ranges, so you can use
.End() on these fields...
.End(false) when it suits you to get the end date-time + 1 second 😵
At this point, it's worth mentioning that you may need to consider how timezones work in Fibery if you're using date-time ranges in formulas, but that's a whole other story, and now it's just time for a cup of tea and a lie down ☕🛏😴