DATEDIFF: calculate Years, months and days at same time

[roattw]

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?

[MayMusic]

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'

 

[roattw]

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.

[MayMusic]

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'

 

[roattw]

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)

 

 

[ManokNaPula]

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!

[Glitch]

In addition to the last comment,

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

Kindly visit this article.