Format and calculating days between dates

[Norma1890]

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?

[#2]

@Norma1890 

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.

[#3]

I have a few steps to suggest to you;

1. Enter Dates in a Consistent Format: Enter your dates in the format "mm/dd/yyyy" (month/day/year) or "dd/mm/yyyy" (day/month/year) to avoid ambiguity. For example:

   • January 1, 2012: Enter as 01/01/2012 or 1/1/2012
   • March 31, 2012: Enter as 03/31/2012 or 31/3/2012

2. Use the DATE Function: If you're concerned about different interpretations of dates, you can also use the DATE function to explicitly specify the year, month, and day. For example:

   • January 1, 2012: =DATE(2012, 1, 1)
   • March 31, 2012: =DATE(2012, 3, 31)

3. Calculate Days Between Dates: To calculate the number of days between two dates, you should use the DAYS function correctly spelled as DAYS, not Days. So your formula should be:

=DAYS(A3, A2)

This formula calculates the number of days between the date in cell A3 and the date in cell A2.