Age calculation

[Jean-Marie Bellec]

Hi Brendan !
Sorry to post this but I don’t find the way to calculate the age of a person.
I’ve found this formula : ($now – [Birth Date]) / 60 / 60 / 24 / 365
But doesn’t seems to work, if I use a french Tap Forms do I have to translate ?
Where can I find the right way ?
Thanks in advance !
Cordialy,
Jean-Marie

[Brendan]

Hello Jean-Marie,

The $now is just a keyword that must be in English. But in Tap Forms 5.1 you can also just use the NOW() function to get the current date. Also in English.

The [Birth Date] part is the name of a field in your form. So if you’ve given it a French name, then the French name is what you’ll use. If you’re using the Mac version, don’t put the square brackets around the field name. Just double-click on the Birth Date field in the list of fields on the left to insert it into your formula. It should work. For the iOS version you need the square brackets around the field name.

[Jean-Marie Bellec]

Hi Brendan,

Well, it seems that I don’t understand how it works…
Please take a look to the joined picture : what is not correct ?
Thanks for everything you do for helping !

Attachments:

You must be logged in to view attached files.

[Jean-Marie Bellec]

Hi Brendan,
Sorry to disturb you so much, I’ve found the solution with your video on Vimeo.
Thanks a lot for the wonderful work !

[Brendan]

I’m glad you found the solution. Your formula was incorrect in the screenshot.

( NOW() – [Birth Date] ) / 60 / 60 / 24 / 365.25

[Neil Alecock]

Brendan. I have copied this formula to get an age from DOB. (NOW()-DOB
) / 60 / 60 / 24 / 365.25

It actually gives me a year older in the calculation.

Can you help?

Thanks

[Brendan]

I would recommend setting the Number Format to Decimal and set the Decimal Places value to 1 or 2. It may be that Tap Forms is simply rounding up.

[Neil Alecock]

Thanks,

That worked, though I have a person who is 16.7 years of age haha. That will do though. Cheers

[Brendan]

In a forthcoming update I’ve added a Years, Month, Days Number Formatter so Tap Forms will display the result in that format, e.g. 16 years, 3 months, 2 days. But the months calculation is an approximation. I used an average number of days/month value to calculate that. Because I’m converting seconds to years, months, and days.

[Mike Schwartz]

Brendan,

If the months calculation is based on an average number of days/month (e.g., 30.4) then the days calculation, which is essentially a remainder, would also be an approximation. The notion of a single-argument conversion function, in the sense of “Convert 2000 days into years, months, and days” just does not make sense because different months (and years) contain different numbers of days. An approximate calculation might not be useful, and might be misleading.

On the other hand, the difference between two specific dates will always have a single, unambiguous solution that can be expressed in years, months, and days. If you can create a two-argument function that provides the exact difference between two specific dates, which is what Jean-Marie was looking for, that might be useful.

[Brendan]

Yes, that’s true. Right now the math parser I use converts all dates into the number of seconds between the date and January 1, 1970. That’s a built-in method actually, so I would think it would take into consideration the leap years, etc. But yes, I am using 30.4 as the number of days in a month.

And you’re right, it would be better to have a more exact date calculation function that treats the dates as dates and not as seconds from the Unix Epoch date.

[gzamba]

Hello Jean-Marie,

I have been using the following calculation and it works fine

($now-DATE-OF-BIRTH)/60/60/24/365
set the result type to number
and then, set the number format to Decimal Style, and the decimal places to 0

[Mike Schwartz]

Brendan,

In case you’re interested, here’s a link to a code sample for calculating the difference between two dates in years, months, and days:

https://www.codeproject.com/Articles/28837/Calculating-Duration-Between-Two-Dates-in-Years-Mo

That’s the full URL despite appearing truncated. The method appears to be sound. It accounts for leap years, but it does so by referencing a “DateTime.IsLeapYear” function that seems to be undefined. But determining leap years is easy: a year is a leap year if it is divisible by 4, but not if divisible by 100 unless if it’s divisible by 400.

Regards,
Mike

[Mike Schwartz]

gzamba,

Your formula approximates the number of years between two dates, but then it gets rounded either up or down to the nearest integer by setting the number of decimals to zero. So if a person is 20 years and 8 months old, your calculation field will display 21 due to rounding up. If you’re caught serving liquor to someone who is 20 years and 8 months, the judge won’t buy that it was “rounding error”. ;-)

— Mike

[Brendan]

Hi Mike,

In the next update I’ve added a few new date functions to the Math parser. You’ll be able to use MINUTES(X,Y), HOURS(X,Y), DAYS(X,Y), MONTHS(X,Y), YEARS(X,Y), YYMMDD(X,Y), YYMM(X,Y), and MMDD(X,Y). The latter 3 will return a Text value, e.g. “2 years, 3 months, 1 day”, “2 years, 3 months”, and “1 month, 2 days”. The other functions will return a Number value.

These new functions take into consideration leap years because I’m using the foundation’s date class NSDateComponent to get the result of determining the difference between the 2 date values. X would be the Start Date and Y would be the End Date.

So to get someone’s age you would just make a formula like: YYMMDD(Birth Date, NOW())

This will be in Tap Forms 5.1.5 for Mac and Tap Forms 5.1.3 for iOS. But I’m not ready to release it yet because I’m still working on a bigger feature that’s going into this build. Hopefully I’ll get a beta out sometime at the end of next week.

Thanks,

Brendan

[Mike Schwartz]

That sounds fantastic! One thing to watch out for: I know that dates are stored internally as date+time, and that you set the time to midnight when the user wants to have date-only. If the NOW() function returns the value of “Now” to the nearest second, then the YYMMDD(Birth Date,NOW()) calculation might return different results depending on whether it is currently before noon or after noon, due to rounding to the nearest day. Maybe we also need a “NOWDATE()” function that truncates to the previous midnight.

Also, here is a link to a web-based date calculator that might be useful for confirming calculation results during testing: https://www.timeanddate.com/date/duration.html

Regards,
Mike

[Brendan]

You’re absolutely right. Before noon gets one value and after noon gets a day later. Just tested it. Before noon got one day earlier. After noon was correct. Bento had both a NOW() and a TODAY() function. So maybe it would be better to have TODAY() instead of NOWDATE() to return the current date with the time set to midnight (end of day).

[Mike Schwartz]

Totally agree that “Today” sounds much better and more intuitive than “Nowdate”.

I can see that you’re setting the time portion of a date-only field to midnight, which is the beginning of the day, not the end: I did a little test in my sandbox form by creating a date-only field, a number field, and a calculation field that returns the sum of the two fields as a date. If I set the date-only field to “7/13/2017” and the number field to 65, then the calculation field returns “Jul 13, 2017, 12:01:05 AM”.

So based on that, I would think that the “TODAY()” function should truncate down to midnight, the beginning of the day, to work well with date-only fields.

One other thing: Do all these date fields and functions accommodate varying time zones? If your cousin from Toronto comes to visit you in Calgary, will the date fields on his laptop subtract a day because midnight converts to 10:00 PM the day before?

Thanks,
Mike

[Brendan]

Yes, TODAY() truncated to the beginning of the day makes sense.

Well, I don’t have any cousins in Toronto. They’re mostly all in England and Ireland :)

Tap Forms stores dates in the database in GMT. For example, a Date & Time field I have that is displayed as Jul 14, 2017, 12:00:00 PM is stored in the database raw as 2017-07-14T18:00:00.000Z. I’m currently 6 hours behind GMT, so all dates get stored with 6 hours added to them so they are in GMT. This isn’t something I’m doing explicitly. It just happens because I’m using the user’s current time zone for dates, which is just the default and the database stores it in GMT.

Now, when I switch my time zone to Toronto, I see the same date displayed as Jul 14, 2017, 2:00:00 PM.

But if I create a new record in the Toronto time zone for the July 14 date (no time), it’s stored in the database as 2017-07-14T04:00:00.000Z because Toronto is only 4 hours behind GMT.

So the date displayed will change when you switch time zones.

When a calculation is evaluated and references date values, it will read in the date value as it was stored in the database in GMT. So if you had a Start Date that was entered in Calgary at noon and an End Date that was entered in Toronto at noon, the difference between the dates will be 2 hours, even though you set them to noon in each time zone. But the display will correctly show the 2 hour difference between the dates. If you edit the Calgary start date while in Toronto, that’ll of course update the time zone to be Toronto’s.

Dates and time zones and all that are really complex problems to solve. Fortunately far smarter people than I have provided the tools necessary to deal with them. I just have to make sure I’m using them correctly. But it seems to be working properly right now.

It would probably be good if the user could specify the time zone when entering a date and tell Tap Forms to display the time zone next to the date/time. But I’m not sure if people would want to see the date in their current time zone or in the time zone it was recorded at. Calendar does both. It shows MDT next to times on the main view even if the events are for different time zones. When you view the event details, then you see the true time zone.

[Jean-Marie Bellec]

and what about this mysterious calculation ?

Attachments:

You must be logged in to view attached files.

[Brendan]

Perhaps that value was imported from a file?

Click the Refresh button to refresh all the calculations and that should correct it.

[Jean-Marie Bellec]

where is the refresh button ?

[Brendan]

At the bottom of the records list view. It looks like a circular arrow. Also command-R or select Refresh Records List from the View menu.

[Author]

Posts