Age calculation

Viewing 21 reply threads
  • Author
    Posts
  • May 16, 2017 at 10:17 PM #23202

    Jean-Marie Bellec
    Participant

    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

    May 17, 2017 at 12:10 AM #23203

    Brendan
    Keymaster

    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.

    May 20, 2017 at 11:26 PM #23238

    Jean-Marie Bellec
    Participant

    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.
    May 21, 2017 at 12:27 AM #23240

    Jean-Marie Bellec
    Participant

    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 !

    May 21, 2017 at 12:53 AM #23242

    Brendan
    Keymaster

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

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

    July 6, 2017 at 6:27 AM #23655

    Neil Alecock
    Participant

    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

    July 6, 2017 at 10:10 AM #23657

    Brendan
    Keymaster

    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.

    July 7, 2017 at 1:49 AM #23665

    Neil Alecock
    Participant

    Thanks,

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

    July 8, 2017 at 10:24 AM #23670

    Brendan
    Keymaster

    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.

    July 9, 2017 at 9:49 AM #23679

    Mike Schwartz
    Participant

    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.

    July 9, 2017 at 1:02 PM #23684

    Brendan
    Keymaster

    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.

    July 11, 2017 at 2:37 PM #23702

    gzamba
    Participant

    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

    July 11, 2017 at 8:23 PM #23707

    Mike Schwartz
    Participant

    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

    July 11, 2017 at 8:56 PM #23708

    Mike Schwartz
    Participant

    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

    July 12, 2017 at 2:15 AM #23709

    Brendan
    Keymaster

    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

    July 12, 2017 at 8:12 AM #23710

    Mike Schwartz
    Participant

    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

    July 12, 2017 at 1:32 PM #23711

    Brendan
    Keymaster

    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).

    July 13, 2017 at 8:32 PM #23716

    Mike Schwartz
    Participant

    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

    July 14, 2017 at 2:01 PM #23718

    Brendan
    Keymaster

    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.

    September 5, 2017 at 12:34 AM #24418

    Jean-Marie Bellec
    Participant

    and what about this mysterious calculation ?

    Attachments:
    You must be logged in to view attached files.
    September 5, 2017 at 12:42 AM #24421

    Brendan
    Keymaster

    Perhaps that value was imported from a file?

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

    September 5, 2017 at 2:41 AM #24426

    Jean-Marie Bellec
    Participant

    where is the refresh button ?

    September 5, 2017 at 10:23 AM #24433

    Brendan
    Keymaster

    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.

Viewing 21 reply threads

You must be logged in to reply to this topic.