IQ Accounting Solutions

If need to know the number of days between two dates, that is very easy to do in Excel. Just subtract one date from the other. For example if cell A2 has an invoice date in it of 1/1/2015 and cell B2 has a date paid of 1/30/2015, then you could enter use the formula =B2-A2 to get the number of days between the two dates, or 29. Excel may automatically format the cell with the formula in it as a date, which means instead of seeing 29, you’ll see 1/29/1900. But all you have to do is set the formatting for that cell to General or to any number format (right-click > choose Formatting) to make the result show as 29.

If you want to know the number of months or years between two dates, things get trickier. The best way is to use the DATEDIF() function. You won’t find this one in the function wizard so you will have to remember how to type it in yourself. The explanation I heard for this is that it isn’t a standard Excel function, it is only included for backward compatibility with Lotus 1-2-3.

The format for the function is DATEDIF(start date,end date,unit).

Start date and end date are self explanatory. Unit meaning the unit of measure, can be “D”, “M”, or “Y” for day, months, or years respectively. There are 3 additional more unusual options that are explained in the example below.

The table below shows sample formulas using each of the unit options. Column D shows the formula that was actually used in column C to get the result shown in column C. Note that the answer only shows complete units. For example the formula on row 4 shows 5 years, even though the two dates are a little more than 5 years apart. The function does not round. So if the two dates had been 5 years and 364 days apart, the answer would still be 5.