Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

AGEING DOGS USING THEIR DATE OF BIRTH

Topic Labels: Formulas
922 2
cancel
Showing results for 
Search instead for 
Did you mean: 
K9PARTNERS
4 - Data Explorer
4 - Data Explorer

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 
2 Replies 2
AlliAlosa
10 - Mercury
10 - Mercury

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"
)
K9PARTNERS
4 - Data Explorer
4 - Data Explorer

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