• 0

# DATEDIFF: calculate Years, months and days at same time

## Question

In excel if I wanted to know the number of Year and months and days until something I would use:

=DATEDIF(B2,TODAY(),"y") & " Years, " & DATEDIF(B2,TODAY(),"ym") & " Months, " & DATEDIF(B2,TODAY(),"md") & " Days"

In CB calculated fields I can get one of the intervals - year OR month OR day but not all three using:

Datediff(day,GetUTCDate(),[@field:ExpirationDate])

Can I convert that excel datediff to a CB friendly alrternative?

## Recommended Posts

• 0

Try:

`CAST (Datediff(year,GetUTCDate(),[@field: ExpirationDate ]) AS varchar) + 'Years, ' + CAST (Datediff(month,GetUTCDate(),[@field: ExpirationDate ]) AS varchar) + 'Months, '+ CAST (Datediff(day,GetUTCDate(),[@field: ExpirationDate ]) AS varchar) +'Days'`

##### Share on other sites

• 0

You are amazing, thank you.  I had it close.  But it turns out this isnt quite what I intended after I saw it live.  This does in fact give the Year, Months, Days date difference between today and the expiration date in the independent forms of:

1 year until expiration, OR
12 months until expiration, OR
342 days until expiration.

After looking at it I realized what Im hoping is for is closer to the Excel version that refreshes and does a combined summary of years, months, days until expiration like examples:

Item A expires in: 1 yr, 2 mos and 14 days

Item B expires in: 0 yr, 8 mos, 6 days.

Different animal I suspect.

##### Share on other sites

• 0

This is closer to what you are looking for:

`Convert(varchar(10),DATEDIFF(yy, [@field:ExpirationDate], GetUTCDate()) )+ 'year' + Convert(varchar(10),DATEDIFF(mm, [@field:ExpirationDate], GetUTCDate()) ) + 'month'  + Convert(varchar(10),ABS(DATEDIFF(mm, DATEADD(yy, Datediff(year,[@field:ExpirationDate], GetUTCDate()), [@field:ExpirationDate]),  [@field:ExpirationDate]))) + 'day'`

##### Share on other sites

• 0

Always appreciate your help!  Getting closer but not quite.  That formula is giving me a miscount for months and days still. It renders these dates this way:

GetUTCdate date                                Expiration Date:
2/16/18                                                     5/6/2020                                       2 years   27 months   24 days    (Should be   2 yrs, 2 months, 20  days)

##### Share on other sites

• 0

Hi @roattw

I have the same workflow but I somehow found a workaround for this.

I created 3 Virtual fields, one for Year calculation, one for month, and another one for year.

Inside the virtual fields, here are my formula.

For Year:

Datediff(mm, [@field:Birthdate], GetUTCDate())/12

For Month:

Datediff(day, [@field:Birthdate], GetUTCDate())/30 - ([@cbParamVirtual1]*12)

For Day:

Datediff(day, [@field:Birthdate], GetUTCDate()) - ([@cbParamVirtual1]*365) - ([@cbParamVirtual2]*30)

And then I concatenated them all to the field that is on my table:

CONVERT(VARCHAR, [@cbParamVirtual1]) + "YEARS " +
CONVERT(VARCHAR, [@cbParamVirtual2]) + "MONTHS " +
CONVERT(VARCHAR,[@cbParamVirtual3]) + "DAYS"

This was my output.

I also hid the Virtual fields using HTML Block so it won't show on the DataPage.

Hope this helps!

##### Share on other sites

• 0

In addition to the last comment,

Here's another How-To article on how to use the DateDiff function.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.