Formulas with dates in tables

What I’m trying to do

I’d like to create a formula in a table that calculates the duration between two dates listed in separate columns. It should automatically retrieve dates from corresponding rows and output the total number of days from the start date to the end date. For instance, if the start date is 2024-01-01 and the end date is 2024-01-02, the formula should return 2 days. Additionally, I would like this formula to work with links to my daily notes.

Things I have tried

This gets me the right output, but I have to input each day twice, once in start/stop and once in total days.

| Start      | End                                    | Total Days                                    |
| ---------- | -------------------------------------- | --------------------------------------------- |
| 2024-01-01 | 2024-01-02                             | `=(date(2024-01-02)-date(2024-01-01)).days+1` |
| 2024-02-05 | [[../journals/2024-02-07\|2024-02-07]] | `=(date(2024-02-07)-date(2024-02-05)).days+1` | 

I am willing to use whatever plugin works.

It’s hard to get values out of tables, but it’s easy to produce tables based on values… So try the following in a note of its own, and see if that could possibly help you on your way:

Tags: f76526
- start: 2024-01-01
  end: 2024-01-02
- start: 2024-02-05
  end: "[[2024-02-07]]"

TABLE WITHOUT ID Start, End, (End - Start).days + 1 as "Total days"
WHERE file = this.file
FLATTEN ranges as range
FLATTEN choice(typeof(range.start) = "link",, 
  date(range.start)) as Start
FLATTEN choice(typeof(range.end) = "link",,
  date(range.end)) as End

In my test vault this displays as:

With all the date formats following the suggested format of Settings > Dataview > Date format.

Thanks, this works great.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.