Format and calculating days between dates

Copper Contributor

I am trying to create a list of dates and calculate the days between the dates.  I have tried to enter the date January 1, 2012 by entering 1/1/12 and it returns 1/1/2012.  When I enter the next date in the column (March 31, 2012) it returns 31/3/12.  


I then try to calculate the days between the dates by entering =Days(A2,A3) and it is returning #VALUE!


What am I doing wrong?

1 Reply


For Excel's DAYS function, the first parameter is the end date, and the second parameter is the start date.  (Poor design on their part, IMO.)  You probably have the two reversed.

As for the date showing 31/3/12, you may have inconsistent date formatting.  Select that cell and press Ctrl + 1 (or click Format on the menu bar and then Font) to get the Format Cells dialog.  On the Number tab, you will probably see a Custom format of d/m/yy. If you wish to change that, click on "Date" under Category and then select whatever entry under Type produces the displayed value that you want.  Click OK.

You can do the same for any other cells for which you want that date format, e.g., by selecting them all at once and following those same steps.