Format years, months, dates between dates

[Jellyhead]

Hello good people - I've an 'easy;' one whipping my butt.

I have two random dates, I need to display how many years, months, days have passed between the dates.

I've been playing around with DateDiff and its all fine to "year" level, but days and months are killing me.  I tried one of Ben Nadel's techniques, but I end up with a "12" on my month counter when it should be "0".  (For the record, for testing purposes I had my two dates precisely a year apart, and got 1 year and 12 months... erm, no!)

Yes, I have searched these forums (and others).  Yes, I got lost as none of them are helpful, either over complicared, or too siimple "Just use datediff()".  

I cannot beleive somehting this simple (which I can do in Excel* in 12 seconds) has now been taking all weekend on Coldfusion.

*Example of Excel code which is working 100%
=DATEDIF(E6,B!, "y") & " y " & DATEDIF(E6, B1, "ym") & " m " & DATEDIF(E6,B1, "md") & " d."

[BKBK · Accepted solution]

No need to worry. Despite the similarity in name, ColdFusion's dateDiff() is different in behaviour from Excel's dateDif. 

Suppose you apply dateDiff to two dates that are a year apart, using the respective date-parts yyyy, m and d. Then you will get the respective results 1 year, 12 months and 365(or 366) days. That is because dateDiff returns the difference in terms of the datepart specified.

Unlike CF's dateDiff, Excel's dateDif is equipped with "unit" arguments, md, ym, yd. With md the months and years in the dates are ignored; with ym the days and years in the dates are ignored; with yd the years in the dates are ignored.

Excel's dateDif is therefore closer in behaviour to ColdFusion's datePart. An example to find the difference of two dates a year apart:

<!--- Difference between two dates a year apart --->

<!--- Dates as strings --->
<cfset date1="16/05/2021">
<cfset date2="16/05/2022">

<!--- Convert each string to date object, because datePart will require a date object as argument --->
<cfset date1=parseDatetime(date1)>
<cfset date2=parseDatetime(date2)>

<cfoutput>	
	Difference in years: #datePart("yyyy", date2) - datePart("yyyy", date1)# <br>
	Difference in months: #datePart("m", date2) - datePart("m", date1)# <br>
	Difference in days: #datePart("d", date2) - datePart("d", date1)# <br>	
</cfoutput>

[Jellyhead]

BRILLIANT!!  Thank you.  I'll email you some beer.  

This works 90%.  Question though: Why do I sometimes get negative numbers on my days and months though?  

(I'm not a coder, I'm a designer who can code a bit.  this stuff makes me realise how out of my depth I am!)

[BKBK]

BRILLIANT!!  Thank you.  I'll email you some beer. 

By @Jellyhead

Cheers!

This works 90%.  Question though: Why do I sometimes get negative numbers on my days and months though?

If month in date1 is November (value: 11) and moth in date2 is May (value: 5), then the difference in the months will be:

5 - 11

which is -6.

If you want the absolute value of the differences, then you can use the following variation of the above code:

<cfoutput>	
	Difference in years: #abs(datePart("yyyy", date2) - datePart("yyyy", date1))# <br>
	Difference in months: #abs(datePart("m", date2) - datePart("m", date1))# <br>
	Difference in days: #abs(datePart("d", date2) - datePart("d", date1))# <br>	
</cfoutput>

[BKBK]

Did that answer your question?