Calculate age with dataview, rounding up

Hi ! i have a problem with dataview that’s driving me nuts…

What I’m trying to do

I have a vault full of notes about people i work for. They have their birthdates in the file properties, and I’m trying to get a table with all of them with a few summary informations, including their age (for example, i need to see which kids are getting 18 in the near future).

But every solution i’ve seen comes with downsides that makes them unusable for me.

Basically, i need a formula that gives me the age, the answer can be “17 years, 11 months, 14 days, 2 hours, 50 minutes, 4 seconds, 303 miliseconds” for all i care, but i only want “17 years” to show up.

Things I have tried

i’ve seen many posts around here, on reddit and elsewhere, that (from my very basic comprehension of dataview) basically come down to two main options with variations.

date(today).year - birthdate.year as age

this takes the year we’re in and substracts the year of birth. In the example above, it would get me 18 today, but 17 last december.

round(dur(date(today) - birthdate).years, 0) as age

this calculates the age with precision to the day and rounds it. Mathematically. so here, below .5 it’s 17, above it’s 18. I can get some more decimals and get 17.45 but it’s not very useful, and i can’t get a table of all 17 years old, because if i add the line “where age =17”, those that are note exactly 17 won’t show

Basically i need “without the decimals” rather than “rounding up”. Is it possible ?

Thanks a lot

Look at durationformat(duration, string) if it helps you.

Cheers, Marko :nerd_face:

1 Like

It WORKS. I knew it could not be that complicated. Thank you soooo much :sweat_smile:

I can now have a table with the correct ages with this :

table birthdate, durationformat(dur(date(today) - birthdate), “yy ‘years old’”) as age

and it shows the name (filename), the birthdate and the age.

The only problem is if i want to select a specific age. I cannot use the formula above with the “where” line, when i could with the other formulas.

specifically, this gets me “no results to show” :

table birthdate, durationformat(dur(date(today) - birthdate), “yy ‘years old’”) as age
where durationformat(dur(date(today) - birthdate), “yy”) = 17

(it doesn’t work either if i keep ‘years old’ in the second line, or if i change ‘=17’ for ‘<17’, if i put ‘>17’, i get the whole list, whatever their age…)

Any idea ?

1 Like

The format functions returns text so either you need to compare it against a text like ... = "17", or you need to convert it back to a number and do number(durationformat(...)) = 17

2 Likes

That is per-fect.

Thanks a lot !

the final formula, which works, is then :

table durationformat(dur(date(today) - birthdate), “yy ‘years old’”) as age, birthdate
where durationformat(dur(date(today) - birthdate), “yy ‘years old’”) =“17 years old”

i have other info in mine, but regarding my question, that’s it. Simple and clean.

Will update OP :slight_smile:

1 Like

And just to clean it up a little, instead of doing that transformation two times, you could write that as:

```dataview
table age,  birthdate

FLATTEN durationformat(dur(date(today) - birthdate), "yy 'years old'") as age
where age = "17 years old"
```

nice, thanks for all your help !

I stumbled upon a bug, which i found almost poetic. One of “my” youngs is almost 18, in two days actually. In the table, she appears as 18. ok, rounding error, i guess. So I try to display more details. she is, according to the table 18 and a day. hmm weird.

so i did a little test and i found this :


the code is as following (but whith the clean up proposed, the result doesn’t change (which i would say is good ? :sweat_smile: )

table durationformat(dur(date(today) - birthdate), "yy 'years', M 'months', d 'days'") as age, birthdate
from "suivis/test"
sort birthdate ASC

Truthfully, it doesn’t bother me, i’m not sure i need a solution for this, it’s just… bizarre. I did not do a thorough testing of all dates, but it does this around the anniversary, be it 10, 12 or 18, but as far as i know not on any other day.

I will keep my test files and check on the 24 and on a couple days after, just to know how it goes.

I do not have answers for that one, but I remembered this one … :smiley:

Cheers, Marko :nerd_face:

I see that you use ‘days’ … if you use only ‘d’, Dataview will automatically display one day as “day” and not “days”.

Loot at this: Literals - Dataview

Same with years, months,…

Cheers, Marko :nerd_face:

Thanks for the answer (and lol, great one, i don’t need that much precision, though :rofl: )

I’m not sure i understand your answer, the ‘days’ is a string corresponding to the words in the screenshot, i use ‘d’ to display the days.

Anyway, i did a test with the dates around today, here it is :

So in short : for the 3 days before the date, it glitches, on the date it’s fine and after that all good.

I’ll leave it here for the posterity, if anyone as an idea on what kind of a hallucination this is :dizzy_face: