Guide
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.
Fibery stores 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, use the following formula:
[Date Range].Start()
If you want the end date, as shown on the UI, use this formula:
[Date Range].End()
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 .Start() and .End() on these fields...
...and/or use .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 ☕🛏😴
Here how final formula may look like:
DateTimeRange(([Due Date].Start() + Days(7)) + Hours(1),(([Due Date].End() + Days(7)) + Hours(1)))
FAQ
Is there a way to apply automation on the date range that will only change the end or the start?
Yes.
🛠️ Explanation step-by-step
DateTimeRange(start, end)
Creates a new date range, defined by a start date and an end date.
[Step 1 Author].Date.Start()
Takes the start date from the “Date” field of the Author created in Step 1.
[Step 1 Author].Date.End()
Takes the end date from the same field.
+ Days(7)
Extends the end date by 7 days.
The formula builds a new date range that starts at the same start date of the original Author’s date range, but the end date is extended by one week.
So, if the Author’s original date range was:
Start: March 1
End: March 5
The new date range will be:
How can I determine if a vacation falls within the current month?
A vacation is considered to be within the current month if any of the following conditions are met:
The vacation starts within the current month.
The vacation ends within the current month.
The vacation spans across multiple months, including the current month (i.e., it started before and ends after the current month).
Thus, formula will look like this
VacationDateRange.Start() <
Date(
If(Month(Today()) = 12, Year(Today()) + 1, Year(Today())),
If(Month(Today()) = 12, 1, Month(Today()) + 1),
1)
and
VacationDateRange.End() >=
Date(
Year(Today()),
Month(Today()),
1)