[Solved] Calculate age and round up at half year

[nexus600]

Hey Community,

I am trying to solve the following problem in my spreadsheet:

My program exports dates of birth as YYYYMMDD and I need that to stay as is. I was able to fill a column with todays date with: =TEXT(TODAY();"YYYYMMDD") and use that,
next column I want it to calculate the age based on todays date which I was able to with =ROUNDUP((todayfield-dateofbirth)/10000) which spits out the age

Because later in the sheet I calculate rates based on the age, I need it to roundup the age further once reaching half a year. So for example if the age is 24 years and 7 months old, that the age shows up as 25. If the age is 24 years and 3 months old it should stay 24.

Anyone who can point me in the right direction of doing this? I'm at the edge of my spreadsheet knowledge here. Thanks a lot in advance!

[RusselB]

First off, Welcome to the Forums
Here's my suggestion in a step by step.
1) Forget about that TEXT function formula, it will create more problems. You can just enter

Code: Select all

=today()

and then (if you want) format it using YYYYMMDD using the cell formats or a spreadsheet style or just forget about entering today's date at this point...it's not required.
2) Can you confirm that the program is exporting the date as a date that is formatted as YYYYMMDD rather than text that appears to be a date in that format?
3) If you can confirm it, then you can calculate the difference by using something like

Code: Select all

=round(year(today())-year(A2)+((month(today())-month(A2))/12);0)

NOTE: The formula you have given, indicates to me that the data your program is exporting for the date is not being interpreted by Calc as a date, but as a YYYYMMDD number... which, if true, would make this problem more complicated.
If you can upload a copy of your spreadsheet with some dummy data, that would help us to determine if it's easier to work with the data as you are getting it, or work out how to convert it (if necessary) to a form that works with formulas like I have posted. See [Forum]How to attach a document

[Villeroy]

My program exports dates of birth as YYYYMMDD

Any spreadsheet program interpretes a sequence of 8 digits as integer or text. Calc knows only these 2 data types and formulas may return error values as a third type.
If you manage to import/convert the 8 digits to the correct day number in cell A1 (spreadsheet dates are day numbers), then =ROUND(YEARS(A1;TODAY())) should do the trick.
If your 8 digits are text: =ROUND(YEARS(DATE(VALUE(LEFT(A1;4));VALUE(MID(A1;3;2));VALUE(RIGHT(A1;2)));TODAY())) splitting the digits in 3, converting to numeric values, calculating the corresponding day number and then the years between the 2 dates.
Having an 8-digit integer, the text formula might work as well since LEFT, MID and RIGHT should do the implicit conversion correctly.

=ISNUMBER(A1) tests if A1 has a number or not (text|blank|error)

[MrProgrammer]

Hi, and welcome to the forum.

My program exports dates of birth as YYYYMMDD and I need that to stay as is.

You did not explain how you imported your data to Calc. Did you use copy/paste? Did you open a text file with Calc? Various options for that are explained in [Tutorial] Text to Columns. Best would be to import the data as dates, but numbers or text are also possible. I've attached a spreadsheet with three sheets, one for each option.

I calculate rates based on the age, I need it to roundup the age further once reaching half a year.

To calculate the rounded age, I think it's simplest to use the MONTHS function.

201807171231.ods

(11.74 KiB) Downloaded 50 times

=ROUND(YEARS(A1;TODAY())) should do the trick.

YEARS requires three parameters and always returns an integer.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[keme]

To calculate properly with date differences, you need to use proper date values. The simplest way to convert your date format to such values is by inserting date separators to make it a valid and unambiguous ISO date string, then use the DATEVALUE() function.

The datevalue difference will be a count of days. Divide by 365,2425 to convert to "average year count". With this, the leap day is "spread out" so the rounding transition may be off by a day. If you need age to always ascend on the exact half-year date you need the strategy outlined by RussellB, expanded to take days into account also, or something similar.

See attached file.

[MrProgrammer]

Divide by 365,2425 to convert to "average year count". With this, the leap day is "spread out" so the rounding transition may be off by a day.

To avoid having the count be "off by a day", we can use Calc's YEARS and MONTHS functions, which understand precisely when leap years occur and perform the difference calculation correctly for all pairs of dates. Since the OP wants to round up/down at a half year, I think the MONTHS function is the correct choice for this situation. We can divide by 12 to get the year and a fraction, then use ROUND. This is illustrated in my attachment above. I will investigate any case presented where rounding MONTHS()/12 does not seem to produce the correct age.

[keme]

Divide by 365,2425 to convert to "average year count". With this, the leap day is "spread out" so the rounding transition may be off by a day.

To avoid having the count be "off by a day", we can use Calc's YEARS and MONTHS functions, which understand precisely when leap years occur and perform the difference calculation correctly for all pairs of dates. Since the OP wants to round up/down at a half year, I think the MONTHS function is the correct choice for this situation. We can divide by 12 to get the year and a fraction, then use ROUND. This is illustrated in my attachment above. I will investigate any case presented where rounding MONTHS()/12 does not seem to produce the correct age.

If matching day numbers is desired (birth date on the 7th means that half-year date should also be on the 7th, regardless of month), I guess the MONTHS()/12 solution is as good as you can get.

I assumed that the desired "half year" should be at count of days, so the year would be divided in two equal parts. With your solution used under that assumption, the majority of half year dates will be off by a day or two. As far as I can tell, the "crossover" date will cover the true half year point for only two months each year. This happens because the 31-day months are not evenly distributed, amplified by February being "on the short side of the circle". That is why you have to take days into account.

Also worth mentioning: the deviation is only present for a day or two each year (not the same date for all people on record), so it may well be that the difference is not significant for the purpose originally presented. All solutions suggested here will be much closer than what could be had with the initial setup.