Hi,
I have a field that stores the year a building was built. E.g. 1998. I want to have another field to auto calculate the age of the building. e.g. Current year (i.e 2018) minus the year built (e.g. 1998). In this example, the field will display “20”.
How do I do that?
Thanks!
Aik Pin
To be more specific, can you show me the formula to use on the calculation field?
Thanks.
Hello Aik,
In the most recent versions of Tap Forms 5 (I forget when I added it), you can use the YEARS() function to get the number of years between two dates.
E.g. YEARS(Build Date; TODAY()
That will return a Number value.
To be more precise, you can use this formula:
YYMM(Build Date; TODAY())
That returns Text of the number of years and months between the dates.
E.g. 10 years, 3 months
Hi Brendan,
I tested with
YEARS(Year; TODAY())
where Year is the name of the field.
No matter whether the year is 1991,1996, 1992, it all returns “48”.
Any idea why?
Thks!
Aik Pin
It should work. I just tested it. But if you provide an empty date, then you’ll get 48 because that’s how many years have passed since January 1, 1970 00:00:00 GMT.
You can modify the formula like this:
IFEMPTY(Year; 0; YEARS(Year; TODAY()))
That will return 0 if Year is empty.
Mmm…it stills return 48.
The “Year” is set as Type “Number”.
Does it make any difference?
Attachments:
You must be
logged in to view attached files.
Yes. Year should be a date field for my formula to work. Sorry I didn’t realize your Year field was a Number field.
So instead, you need to use the NEWDATE() function to convert your Year field into a Date field.
NEWDATE(Y;M;W;WD;WO;D;H;M;S)
Y = Year, M = Month, W = Week of Year, WD = Weekday, WO = Week Ordinal, D = Days, H = Hours, M = Minutes, S = Seconds.|Date
So what you could do is:
IFEMPTY(Year; 0; YEARS(NEWDATE(Year; 1; 0; 0; 0; 1; 0; 0; 0); TODAY())))
That will convert your Year field into Year-01-01, where Year is the field. So basically January 1, whatever year from the Year field.
Now that you have 2 dates, you can use the YEARS() function to calculate the years between the dates.
I guess it would be useful to have functions to extract out the units from a Date field as a number value.
E.g. YEAR(TODAY()) would return 2018, MONTH(TODAY()) would return 3, etc.
.
