Calculating months between two dates issues wrong values

[Isabel Hostettler]

Hi all,

I am using stata 15.1

a general thing: I have realized that when I use the following command to create a difference between two dates of months I get wrong results:

gen FU_mt=(DatumLetzterBesuchimRDI-OPDate)/12

I do get the right days when calculating date difference in days:

gen FU_d=(DatumLetzterBesuchimRDI-OPDate)

For example (12sep2016-12aug2016)/12 gives me 2.583 months
12sep2016-12aug2016 correctly gives me 31 days

I have checked stata forum and google and I dont get to the solution. What am I doing wrong?

[William Lisowski]

What answer do you want? 31/12 gives 2.583. But dividing days by 12 does not yield months.

I've often seen the average number of days in a month calculated as 365.25 (the average number of days in a year) divided by 12 (the number of months in a year) = 30.44.

But since both of your example dates are the same day of the month, I'm wondering if the correct approach isn't (2016m9 - 2016m8) where the two values are Stata Internal Format monthly dates rather than daily dates.

. generate mon1 = mofd(dt1)

. generate mon2 = mofd(dt2)

. format %tm mon*

. generate diff = mon2 - mon1

. list, noobs

  +------------------------------------------------+
  |       dt1         dt2     mon1     mon2   diff |
  |------------------------------------------------|
  | 12aug2016   12sep2016   2016m8   2016m9      1 |
  +------------------------------------------------+

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

[Isabel Hostettler]

Thank you very much for your help.

From the below post:
https://www.statalist.org/forums/for...ween-two-dates

I understood that days divided by 12 would give you months. But this is not correct, right? What about when dividing by 30? Because substracting to dates and dividing it by 365.25 does give you years.

I have tried using your code applying it to my data:

gen monOP= mofd(OPDate)
gen monFU=mofd(DatumLetzterBesuchimRDI)
format %tm mon*
gen diff=monFU-monOP
br FU_mt FU_d diff OPDate DatumLetzterBesuchimRD

I


I see the values it returns (giving me the number of months but just if the months changes) however I need the exact months even if its 0.39 months=12days. The

help datetime

does however not help me in how to calculate months between two dates as far as I can see.

Any other suggestions? Im sorry if its too basic a question but I cant get it right somehow

[William Lisowski]

If dividing days by 12 gave months, then 1 year = 365 days = 30.42 months, so what you understood was incorrect. Dividing months by 12 gives you years.

I wrote in post #2

I've often seen the average number of days in a month calculated as 365.25 (the average number of days in a year) divided by 12 (the number of months in a year) = 30.44.

and this is what I recommend you do since you are in fact interested in fractional months: divide days by (365.25/12).

Putting it another way, divide days by 365.25 to convert days to years, then multiply years by 12 to convert years to months.

[Kreshna Gopal (StataCorp)]

Please note that in a recent update of Stata 16 (on November 5, 2020), the function datediff_frac() was introduced to calculate the difference, including the fractional part, between two dates in days, months, or years. It also factors in whether February 28 or March 1 is the nonleap-year anniversary for February 29. datediff() does the same calculation, but returns the rounded down integer difference. We also introduced several other date and time functions related to clock time difference, age, birthdays, and leap years. More date and time functions are in the pipeline. help datetime functions describes all the date and time functions. help whatsnew lists recently added functions. update all to download the latest updates.

-- Kreshna

[William Lisowski]

Kreshna Gopal (StataCorp) -

Thank you for pointing out the new functions in the recent update. For others reading this, help whatsnew tells us the complete list is as shown below. Additionally, help datetime durations and help datetime relative dates introduce new new sections of the full Stata documentation that discuss in greater depth the first five and final six of these functions, respectively.

    1. New date and time functions are available:

            age(e_{dDOB},e_d[,s_d]) returns age in integer years.

            age_frac(e_{dDOB},e_d[,s_d]) returns age in years including the
            fractional part.

            clockdiff(e_tc1,e_tc2,s_u) returns time difference, rounded down to
            an integer, in units of s_u.

            datediff(e_d1,e_d2,s_1[,s_2]) returns date difference, rounded down
            to an integer, in units of s_1.

            datediff_frac(e_d1,e_d2,s_1[,s_2]) returns date difference, including
            the fractional part, in units of s_1.

            birthday(e_{dDOB},Y[,s_d]) returns date of birthday in year Y.

            previousbirthday(e_{dDOB},e_d[,s_d]) returns date of previous
            birthday.

            nextbirthday(e_{dDOB},e_d[,s_d]) returns date of next birthday.

            isleapyear(Y) returns 1 if Y is a leap year; otherwise, 0.

            previousleapyear(Y) returns the leap year immediately before year Y.

            nextleapyear(Y) returns the first leap year after year Y.

        Functions age(), age_frac(), datediff(), datediff_frac(), birthday(),
        previousbirthday(), and nextbirthday() each have an optional argument to
        handle dates starting on 29feb.