Number of Days between Two DatesDate: 09/06/2004 at 08:54:15 From: S. Ramachandran Subject: given two dates-finding no.of days between Given two dates, how can I mathematically find out the number of days between them? In Microsoft Excel there is a function called datedif(old date,new date,"d") and it finds the number of days. I want to know the math behind that function. Date: 09/07/2004 at 11:00:53 From: Doctor Vogler Subject: Re: given two dates-finding no.of days ,months,years Hi S, Thanks for writing to Dr. Math. That's a very good question. I asked myself that same question some time ago, and I came up with the following formula. It's not the only formula, so I can't say that Excel does exactly the same thing, but it *does* work. Here is the idea: I am going to give you a function that takes in three numbers, a date (1 to 31), month (1 to 12), and year (such as 2004), and it will give back an integer. The integer is the exact number of days from a certain fixed date. (In my case, it will be just slightly BC, but you can change it to another date by adding a fixed constant.) Then all you have to do is compute this function for both of your dates and subtract! There are several ways of coming up with such a function, depending, for example, on how you record the month, day, and year, and what calendar you are using. I will be using the Gregorian Calendar, which is the one in common use in the west (such as America, where I live, and where MS Excel was created). You can find more formulas and information from our FAQ: Calendar; Days of the Week http://mathforum.org/dr.math/faq/faq.calendar.html or by searching our archives for something like calendar date days Now, on to the formula: First of all, since February is an especially short month, it is normally better to consider Jan and Feb the 13th and 14th months of the previous year. So first, if the month is 1 or 2, then you add 12 to the month and subtract 1 from the year. Then the day is 365*year + year/4 - year/100 + year/400 + date + (153*month+8)/5 where all of the divisions are rounded DOWN to the nearest integer. Do this for both dates, and subtract. You can use this formula for another problem too: If you divide by 7, then the remainder corresponds to the day of the week. The days of the week are: 0 = Sun 1 = Mon 2 = Tues 3 = Wed 4 = Thu 5 = Fri 6 = Sat The reason my formula works is this: There are 365 days in most years, so we add 365*year. But every fourth year is a leap year (add year/4) except the century years (subtract year/100) not divisible by 400 (add year/400). Then you add in the date of the month, because that's one day in each month. Those are the easy parts. The hard part is the month, because you need a formula that takes the month (1 to 12, or rather 3 to 14 in our case) to the number of days that have already passed in the year up to that point. It just so happens that the 30-day and 31-day months are spaced apart in such a way that if you move Jan and Feb to the end of the previous year, then (153*month + 8)/5 and rounding down gives you exactly this number. If you don't want to move Jan and Feb, then you can change the formula to 365*year + year/4 - year/100 + year/400 + date + table[month] where you look up a number in a table for each month. This works too, and it means you don't have to subtract one from the year if the month is Jan or Feb, but it means you have to keep a table handy. Here's a similar problem. What if you want to convert a day number into a date? Suppose you have a day number (which I will call day) from the above formula. Start with y = (400*day - 37009)/146097 (and remember to round down). This will *almost* always give you right year. If your date is at the very end of the previous year (like 29 Feb, usually; remember how we moved Jan and Feb?), then this might be off by 1, since leap years make this a little fuzzy. If (400*day - 37489)/146097 gives you the same number, then it is correct. If not, then plug the higher year (day 1 of month 3 of year y) into the above formula and see if that is bigger than "day". If so, subtract one from the year. Now adjust the day number according to the year number, day = day - (365*y + y/4 - y/100 + y/400), (and remember to round down each division). Then the month number is m = (5*day - 9)/153, which (remarkably) will always give you exactly the right month (since the funny month, February, was moved to the end of the previous year). Now adjust the day number again, day = day - (153*m + 8)/5 and what is left is the day of the month, d = day. Now we have to reverse the Jan/Feb thing, so if m > 12, then subtract 12 from the month and add 1 to the year. There are a lot steps to this, but it is all very straightforward, and it lends itself very easily to a computer program. And it's always worked for me! If you have any questions about this or need more help, please write back and show me what you have been able to do, and I will try to offer further suggestions. - Doctor Vogler, The Math Forum http://mathforum.org/dr.math/ |
Search the Dr. Math Library: |
[Privacy Policy] [Terms of Use]
Ask Dr. Math^{TM}
© 1994-2013 The Math Forum
http://mathforum.org/dr.math/