Age Calculations

[Fred Stetler]

Is there a way to calculate a persons age in years, months and days? In “Number Format” I fined Currency, Decimal, Time ect. but no Years/Months/Days.

[Brendan]

Hi Fred,

You can do this, but you would need more than one formula. Or you can just use a decimal value for the age, such as 5.8 years.

The formula is:

($now – Birth Date) / 60 / 60 / 24 / 365.25

Thanks!

Brendan

[Ted McMillan]

I used the formula above to do a calculation to determine the age at death. I subtracted the Date of Death field from the Date of Birth field and divided by 60, 60 again, 24, and 365.5 as shown above. My mother died May 22, 2007. Her birthdate is June 15, 1926. The value displayed is 80 and it should be 79. Any suggestions?

[Brendan]

Hmm… Except when you subtract 1926 from 2007 you get 81. Wasn’t she 81 then? That’s the answer I get.

[Brendan]

Although my result was rounded and it’s actually 80.9 years old. Just add a decimal place to your Calculation field.

[Ted McMillan]

Thanks Brendan
My mother’s birth year was 1927 not 1926 — my entry error. I also added two decimal places. I found that one of the division symbols did not have a space in front of it. All works as expected now. Is there any way to show just the integer portion of the number not rounded off?

Thanks

[Brendan]

Hi Ted,

There are various options you can do to accomplish this depending on the outcome you prefer.

TRUNC: Discards the fractional part of a number. e.g. TRUNC(-3.2) is -3, TRUNC(3.2) is 3.
CEIL: CEIL(-3.2) = 3, CEIL(3.2) = 4
FLOOR: FLOOR(-3.2) = -4, FLOOR(3.2) = 3

So you could add one of the above function around your entire formula.

For example:

FLOOR(($now – Birth Date) / 60 / 60 / 24 / 365.25)

Thanks!

Brendan

[Ted McMillan]

Hi Brendan,

I love Tap Forms! As you know, I was working on getting the current age of a person. I discovered that on a person’s birthday, the age did not change until the day after. In the formula below I added one day in seconds and all works as I expected.

Thanks
Ted McMillan

TRUNC((($now – Birthdate + 86400)) / 60 / 60 / 24 / 365.25)

[brucel]

Brendan,
I am using a db of deceaced ancestors where I use date fields and calculations similar to the above:
($now – [Birthday] )/(365*24*60*60)
It works just fine.
I do have an issue when travelling (I live in Australia and travel to the U.S. (across the international date line so mostly it is the day before in the U.S.)
The issue is not with the calculation but the fact that the date seems to change in the birthdate and date of death (date) fields to the day before the actual date. I assume this is because the date field is sensitive to the Timezone on the device.
It seems to me that this behaviour is not desirable for most applications of the date field once populated. Exceptions might be ‘date modified’ type fields.
Any ideas on how I might prevent this behaviour or is it something for you to consider for a future update?
Kind regards
Bruce

[Brendan]

Hi Bruce,

It’s something I have to think about. Tap Forms stores its date values using the Unix epoch time format. That is, the number of seconds from January 1, 1970. I simply give that value to the operating system to create a date object from the value. The date object takes into consideration the current time zone and displays that. So it doesn’t really fix the time zone to the time zone that it was when the value was entered. Since the number of seconds from January 1, 1970 is a fixed value, I guess it appears to change when you switch time zones.

[Jim Burgess]

This sounds the problem I brought up 6 months ago in this topic:

Record dates change when switching time zones

Has any progress been made in how to properly handle this?

[Brendan]

Hi Jim,

No I haven’t worked on this. The problem as I see it is that I may have to store all the dates as textual representations. E.g. 2015-01-07 01:08:40 GMT (or whatever time zone the user was in at that instant). But that also means having to convert everyone’s date data to that format. And since time zone information isn’t encoded into the Unix epoch times (since it’s just the number of seconds from the reference date), I’d have to just pick the current time zone anyway.

I was doing some research on this and I saw this well described response to someone’s query on http://www.stackoverflow.com:

1313427270.504315 is the number of seconds since 1 January 1970, GMT and this is an absolute measure that does not depend on which timezone you are. It corresponds to 2011-08-15 16:54:30 in GMT and 2011-08-15 09:54:30 in California. Both are different representations of the same instant.

“Both are representations of the same instance”. That was a great way of stating this reality.

And for your informative enjoyment, here’s an awesome video that describes the trouble with time zones for programmers:

Brendan

[brucel]

Hi Brendan,
I appreciate your problem in changing the format of a field type – practically this seems like a dangerous solution since it could through up all kinds of consequences across the user base.
I also appreciate the difficulties of dealing with timezones in software. Complex doesn’t begin to describe it!

One possible solution I thought of us to simply create a new data type for ‘absolute date’ (or call it what you will) which might store the data as an absolute date in a textural representation. Those of us who want to use this could simply convert the date fields to the new format.
I guess that leaves the calculation of elapsed time since that date and I suppose you could convert the text representation back into Unix epoch in the same (current) Timezone as ‘now’ when making the calculation but I am not sure this will not fall foul of our original issue – this is doing my head in!

Regards
Bruce

[Brendan]

Hi Bruce,

Love your avatar :)

That’s an interesting solution. But technically not very user friendly and does complicate usability. Now there’s going to probably be 6 different date field types. Date, Date + Time Zone, Date & Time, Date & Time + Time Zone, Time, Time + Time Zone.

[guylaine]

Hi!

I have a “age” fields in my forms, and I’m able to calculate based on birth date and current date using the above formula.

However, I have individuals ranging from a few days old, to 80 years old in my database, so I’d like to be able to display the calculated age in “x years, x months, x days, x hours”. Any chance that a Duration will be added in the Number format? “Time in hours, minutes, seconds is already available and works fine with the calculation. Maybe I’m missing something, but it’s seems it would be that difficult to push that format forward and add the option of displaying it in years, months, days? It would really help!

Thanks!

[Brendan]

Thanks for the feature request Guylaine. It would be good to have other duration options there. But they also have to work for input as well as output and that’s more work to do.

[guylaine]

I found an (imperfect) workaround using another Checkmark field (Age > 1 yo?) and the following calculation:
($now – Birth Date)/(60*60*24*30)/((11*Age>1yo?)+1)

If the individual is older than 1 year old, I check the box and the formula automatically divides by 12.

Not perfect, since I only get a number, but will do for now. If anyone has an idea on how to add/display “months” or “years” depending on the presence or absence of the checkmark, I’d love to hear it!

[billv]

I have a question regarding calculating a duration of a task or process. If this is the wrong forum for this question I apologize.
In the birthday example ($now – Birth Date)/(60*60*24*30)/((11*Age>1yo?)+1) you are able to calculate the life time of something by using todays date ($now). How do I change todays date to be any date and get the elapsed time between the two dates or the duration of the scheduled process?

[Brendan]

Hi Bill,

You just have to put in a separate field that holds the date. You can then change that field and put in whatever date you want.

Like

(End Date – Start Date) / 60 / 60 / 24 / 365.25

The above would calculate the number of years between end date and start date.

[billv]

Hi Brendan,

I have tried entering the formula as you requested and I made adjustments based on your input via email but I am not having any luck generating an outcome. (End Date – Start Date) / 60 / 60 / 24 / 365.25 (this format copied from your email.)

In my form I am subtracting the finish date from the start date -December 29, 2017 – September 30, 2016 and the result is December 31, 1969. Not sure how I did that. Any help would be appreciated.

Thank you,

Attachments:

You must be logged in to view attached files.

[Fred Stetler]

I must be missing something for age calculation, I’ve used “($now – Birth Date) / 60 / 60 / 24 / 365.25” which gives me age in years and decimals. I would still like it in years, months, days. I tried “YYMMDD(birth date, NOW())” with no results. I’m using an IPhone with TapForms 5.1.2. Any help would be appreciated.

Thanks,
Fred

[Brendan]

Hi Fred,

The YYMMDD(X, Y) date function is a beta feature. It won’t work in the production version right now.

Wait for 5.2 to be released.

Thanks,

Brendan

[Fred Stetler]

Brendan,

I had been using TapForms LT.

Two weeks ago I purchased 4.0.7 from ITunes.

Earlier this week I purchased 5.1.2 again from ITunes. All for the Years, Months, Days option.

Will I now need to purchase 5.2?

I would be willing to try the beta version.

Fred

[Brendan]

No. It will be a free update for all Tap Forms 5 customers. I’ve already submitted it to Apple for review just today. So I’m waiting on them now to review and approve it.

[Fred Stetler]

Brendan,
I am now using 5.2.1 and still having problems with age calculation. In formula, I’m using YYMMDD(Birth Date, NOW()) and Number Format I’ve selected TIME (years, months, days). Age is coming up blank. My Record has Name, Birth Date, Age. With the previous formula ($now – Birth Date) / 60 / 60 / 24 / 365.25 I would get the number of years with a decimal. I’m sure I’m doing something wrong.

Fred

[Brendan]

Hi Fred,

Set the Result Type to Text on the Formula Edit screen and that’ll solve the problem. When you use the YYMMDD() function, Tap Forms returns a Text value. You probably have it returning a Number or a Date. It should be Text.

Thanks,

Brendan

[Christine Murdoch]

Hi Brendan,
If you recognise my email address, you will know that I don’t have a lot of working knowledge of databases – I basically use them to search information that I have built up over the years. However, I am running a summer school for children and it would be really helpful for me to be able to enter a date of birth and have another field automatically calculate the child’s age. I appreciate you have given the formula above (TRUNC((($now – Birthdate + 86400)) / 60 / 60 / 24 / 365.25)), however, what I need to know is what I now do with this. I have tried entering it in a new field that I have assumed I should set to calculation. Do I need to rename the “Date of Birth” field to “birthdate”. Thank you for any help you can give me.
I am now running 5.2.

[Brendan]

Hi Christine,

You should use one of the new date functions now instead of that long one:

YYMMDD(Birth Date; NOW())

or

YYMM(Birth Date; NOW())

or

YEARS(Birth Date; NOW())

It’s up to you.

The first 2 return a Text value, so make sure you have the Result Type set to Text. YEARS() returns a Number.

The above functions also take into consideration leap years and such, so they’re more accurate than the other way.

Thanks!

Brendan

[Simon Smailus]

Is there a new way to do this? I can’t get it to work? I’m in the uk region. Using iOS 5.36

How do I get an age between two dates?

[Brendan]

Hi Simon,

Those functions do give you an age between two dates.

What troubles are you having getting it to work? Do you get no results or incorrect results? Do you have the Result Type set appropriately depending on which function you use? If you have the Result Type set wrong, you won’t get an output or you’ll get the wrong output.

Thanks,

Brendan

[john cesta]

I get my ages with this….

YEARS([Birth Date];TODAY())

[Simon Smailus]

YEARS([Birth Date];TODAY())

Thanks that works. It’s the square brackets that make all the difference!

[Christine Murdoch]

I’m still not getting this to work. I am using Tap Forms 5 on a MBP. What format should I use for birth date and what type should I select for the field? Thank you, Christine

[Brendan]

@Simon, The square brackets are only required in the iOS version because there’s no token object on iOS like there is on macOS. So the square brackets tells Tap Forms what a field is.

@Christine, on the Mac version make sure your field types are Date or Date & Time. Then just double-click on a field in the formula editor to insert it into your formula. If you post a screenshot of what you have so far, that will help us to figure out what’s going wrong for you.

[john cesta]

Simon, always select the field from the drop down don’t try and just type the name in. The drop down will give your everything you need for the field. It will format it properly.

[Christine Murdoch]

Sorry to return to an old topic, but this is still not working for me. I’m using Tap Forms Version 5.3.17 (Build 979). I am cutting and pasting the above formulas (so I don’t make a mistake) and all that happens is that I see the day of birth as “12 July 2006 at 12:00:00”.
Any help would be appreciated. Thank you.

[Sam Moffatt]

Can you take a screenshot of your formula and attach it?

[Christine Murdoch]

Dear Sam,
The formulae I have been using are the ones suggested by Brendon, as per the attached screenshot. I have been copying and pasting them to eradicate errors.
Thank you, Christine

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Christine,

I think Sam meant a screenshot of your own formula edit screen.

The issue may be that if you’re using the Mac version then you need to double-click on your field name in the list of fields to insert it into the formula. If you just copy and paste, then the formula won’t be referencing your date field properly. On the Mac version you should see a field name within a bubble. On iOS the field names are surrounded by square brackets.

Thanks!

Brendan

[Christine Murdoch]

Thank you for the help – should the field type be calculation? I attach screen shots of what happens:

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

You want to use text if you’re using YYMMDD or YYMM because they return a text whilst YEARS returns a number. If you scroll down on the function list on the right, when you click on the function it will tell you what it returns. Generally text is a safe type when debugging because it will at least give you a result. I’ve attached a quick screenshot of a calculation I did quickly and what it looks like.

If you can share what your edit formula window looks like then it will likely help understand what is going on.

Attachments:

You must be logged in to view attached files.

[Christine Murdoch]

Thank you, Simon, I think I have been going about this in completely the wrong way. I was almost at the stage of saying I could do the arithmetic more quickly. Off to (another) Zoom meeting, but I will try this out later today.

[Christine Murdoch]

Oh, am I so near and yet still so far! I now have the function doing something, but getting the wrong answer and the same answer for everyone. I added my own date of brith as in the screen shot, but the answer comes back as 51 years when it should be 56. Also, when I change the date of birth, the answer remains at 51 years and it is the same answer for every record. I think I will just continue to calculate each child’s age by arithmetic. Thank you for you continued help. Brendan has been trying to help me with another issue and I am beginning to wonder if my Tap Forms app is corrupted in some way.
Keep safe,
Christine

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

It doesn’t work like this, Christine. The input to the calculation should be a date value, eg date field. The value you used looks like the date for us, but TapForms’ internal date format is different.

Delete the age you used, and double-click on the ‘Date of Birth’ field on the left side to insert it into the formula. Then the formula should look like YYMMDD(Date of Month; NOW()).

[Author]

Posts