I have found it immensely useful to be able to calculate if today’s date falls within a specific date range. For example, when using sprints to manage my work, I set up each sprint with a two week timeframe using a date property with a range (start date and end date). I can then use a formula to dynamically determine which sprint is the current sprint, and filter to display only tasks for that sprint. Using this dynamic method, I don’t have to create multiple views for each date range, the current sprint and associated tasks will always load. It’s incredibly simple, but very effective.
The Breakdown
This formula assumes that you have a “Dates” property that uses a date range (with a start and end date). When using a date range, we can access either the start date or the end date of the range by using dateStart(prop("Dates") and dateEnd(prop("Dates")).
First, we use the now() function and run it through the new parseDate() function to return today’s date without the current time. By default, the date without the time will return 12:00 AM.
parseDate(formatDate(now(), "YYYYMMDD"))
We can then use a simple if / and statement to determine if today is on or after the start date and today is also on or before the end date. If it is, we return true and display a checked checkbox. Otherwise, we return false and display an unchecked checkbox (using a true or false “boolean” result will give us a checkbox display that is either checked or unchecked).
Take a look:
if(parseDate(formatDate(now(), "YYYYMMDD")) >= dateStart(prop("Dates")) and parseDate(formatDate(now(), "YYYYMMDD")) <= dateEnd(prop("Dates")), true, false)If you wish, you can display something else (like icons or text) in the place of a checkbox, just enclose them in quotation marks. For example:
if(parseDate(formatDate(now(), "YYYYMMDD")) >= dateStart(prop("Dates")) and parseDate(formatDate(now(), "YYYYMMDD")) <= dateEnd(prop("Dates")), "✅", "❗️")Then you can set up a view / filter to display only items that fall within your date range. In my case, I have tasks linked to my sprints, so I use a rollup property to pull the checkbox into the task and then display only tasks that are part of the current sprint.
However, the applications are endless. Enjoy!
