0

I need to display a formula field that shows the age rounded to the nearest half year. For example: if the DOB is 01.01.2012 then that persons age would be 11.5. The formula I'm using only displays 11. Where have I gone wrong?

IF(
    MONTH(TODAY()) > MONTH(Birthday__c),
    YEAR(TODAY()) - YEAR(Birthday__c),
    IF(
        AND(
            MONTH(TODAY()) = MONTH(Birthday__c),
            DAY(TODAY()) >= DAY(Birthday__c)
        ),
        YEAR(TODAY()) - YEAR(Birthday__c),
        (YEAR(TODAY()) - YEAR(Birthday__c)) - 1
    )
)
1
  • 1
    In short, your formula doesn't give you half-years because you have nothing in it that adds or subtracts 0.5 (or divides an odd number by 2). It literally just gives you the year difference (with a correction if you're in the right month but haven't reached the birth date yet).
    – Derek F
    Aug 27, 2023 at 14:02

1 Answer 1

2

I think going at this from a year/month/day approach (like you're currently using) is the wrong way to go about this. I'm sure you could make it work, but I imagine that the resulting formula would be cumbersome.

Instead, I think the approach to use is to work with the difference in days.

Getting an exact answer with this approach would also be pretty cumbersome. If you can live with the result being up to two days early/late to switch over to the next increment, then starting by dividing the number of days by 365.25 should suffice (the "no leap year if it's a new century, unless it's also divisible by 400" rule will come into play at most one time during the life of ~99.3% of people)

So the following gets you the number of years.

FLOOR((TODAY() - Birthday__c) / 365.25)

The only part left is to determine when we need to add half a year. There are a number of ways you could go about that, but I think that getting the number of months mod 4 (i.e. what quarter are we in?) is going to make the formula easier in the end (and also more flexible).

Continuing with our first-degree approximation of leap years, the average month has 30.4375 days (1460 days + 1 leap day in any 4-year period, 48 months in any 4-year period).

So the following gets us the quarter (zero-indexed) of the person's current age (taking the "start" of the relative year to be their birthday).

MOD(
    FLOOR((TODAY() - Birthday__c) / 30.4375),
    4
)

Now that we have the quarter, we just need to decide how much to add to the number of years.

  • Quarter 0 = closer to <age> than <age> + 0.5, so add 0
  • Quarter 1 = closer to <age> + 0.5 than <age>, so add 0.5
  • Quarter 2 = closer to <age> + 0.5 than <age> + 1, so add 0.5
  • Quarter 3 = closer to <age> + 1 than <age> + 0.5

The first three quarters are simple. For the final case, you need to decide if you want to treat them as being <age> + 0.5 or <age> + 1. Taking your requirement literally (round to the nearest half year), I'd say that adding 1 would be appropriate. This also means that the person spends an equal amount of time in each integer year.

The way that I've chosen to structure this (getting months mod 4) makes it fit really nicely into the use-case for CASE().

So the part of the formula to determine how much to add ends up being

CASE(
    MOD(
        FLOOR((TODAY() - Birthday__c) / 30.4375),
        4
    ),
    /* Quarter 0, add 0 */
    0, 0,
    /* Quarter 1, add 0.5 */
    1, 0.5,
    /* Quarter 2, add 0.5 */
    2, 0.5,
    /* Quarter 3, add 1 */
    3, 1,
    /* default value, add 0 */
    0
)

The flexibility here means that if you have an issue with saying someone is <age> + 1 before their next birthday comes, and instead just want to count anything over 6 months as <age> + 0.5, then the overall structure of the formula remains the same.

The only thing you'd have to do in that case is change the CASE() parts:

  • From 1, 0.5, to 1, 0,
  • From 3, 1, to 3, 0.5,

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .