Excel/Open Office Q: finding # of days between 2 dates

I have found several calculators online that will tell you the number of days between two dates. Can this be done in Excel (for work) or Open Office (for home)?

If you put a date in a cell & a date in another cell, you can subtract them from each other , eg A1 has 12/12/2004 & B1 has 12/04/2004 (US date system), & you enter =A1-B1 in C1, it will give you 1/8/1900 - but if you use cell formatting to force that to be a number, it will give you the number of days - 8 between the two days.


Deceptively easy!


If you want more flexibility, and can handle seeing a code window, you can use this approach:

  1. Tools => Macro => Record New Macro => Okay

  2. Type a couple characters and move the cell pointer, then hit the “Stop Recording Macro” button.

  3. Tools => Macro => Visual Basic Editor

  4. Along the left side of the screen, expand “Modules” and open “Module1”

  5. Remove the macro you just recorded, which is probably named macro1. (All lines between and including “Sub Macro1()” and “End Sub”)

  6. Copy and Paste the following text in that module:

Public Function DateDif(pdtm1 As Date, pdtm2 As Date, pstrType As String) As Long
    DateDif = DateDiff(pstrType, pdtm1, pdtm2)
End Function

  1. Debug => Compile

  2. Close the code window and save your spreadsheet.

  3. Now set C1 (from sierra’s example) to =DateDif(A1,B1,“d”) to get the difference in days.

The only reason this would be useful is because you can control the interval. “d” returns the difference in days, but you can also send the following instead of “d”:

“yyyy” returns difference in Years
“q” returns difference in Quarters
“m” returns difference in Months
“d” returns difference in Days
“ww” returns difference in Weeks
“h” returns difference in Hours
“n” returns difference in Minutes
“s” returns difference in Seconds