Jump to content
  • 0
roattw

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?

Share this post


Link to post
Share on other sites

4 answers to this question

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×