Skip to main content
J
Jellyhead
Known Participant
May 16, 2022
Answered

Format years, months, dates between dates

  • May 16, 2022
  • 1 reply
  • 536 views

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."


This topic has been closed for replies.
Correct answer BKBK

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>

 

1 reply

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
May 16, 2022

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>

 

J
JellyheadAuthor
Known Participant
May 16, 2022

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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 16, 2022
    quote

    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>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices