Skip to main content

I've found a formula which is partly working for us, originally it was showing the years and months as negative numbers but I've managed to sort out the years aspect but I can't figure out what needs to change to make the months show correctly.

currently shows the response like this 0 Years, -7 Months

Formula being used is 

DATETIME_DIFF(
NOW(),
{D of B},
'Years'
) & " Years, " &
DATETIME_DIFF(
DATEADD({D of B}, DATETIME_DIFF(NOW(), {D of B}, 'Years'), 'Years'),
NOW(),
'Months'
) & " Months"
 
can anyone read this formula or add to it to make it respond with years, & months 

You just need to switch the order of the parameters in the months part of the formula.

I would also change NOW() to TODAY() - NOW() is known to have a large impact on performance and slow down your base.

Finally, I would wrap the whole thing in an IF() so that you don’t get errors if you don’t have a DOB.


On my phone so the below won’t look super pretty but it should be correct.

IF(
    {D of B},
    DATETIME_DIFF(
        TODAY(),
        {D of B},
        'Years'
    ) & " Years, " &
    DATETIME_DIFF(
        TODAY(),
        DATEADD(
            {D of B},
            DATETIME_DIFF(
                TODAY(),
                {D of B},
                'Years'
            ),
            'Years'
        ),
        'Months'
    ) & " Months"
)

AMAZING _ THANK U so much for this - truly appreciate this!!

Reply


Terms & Conditions